diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/spi.sgml | 306 |
1 files changed, 189 insertions, 117 deletions
diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml index d8c121f5f35..6543eaa0343 100644 --- a/doc/src/sgml/spi.sgml +++ b/doc/src/sgml/spi.sgml @@ -632,25 +632,23 @@ int SPI_exec(const char * <parameter>command</parameter>, long <parameter>count< <!-- *********************************************** --> -<refentry id="spi-spi-execute-with-args"> - <indexterm><primary>SPI_execute_with_args</primary></indexterm> +<refentry id="spi-spi-execute-extended"> + <indexterm><primary>SPI_execute_extended</primary></indexterm> <refmeta> - <refentrytitle>SPI_execute_with_args</refentrytitle> + <refentrytitle>SPI_execute_extended</refentrytitle> <manvolnum>3</manvolnum> </refmeta> <refnamediv> - <refname>SPI_execute_with_args</refname> + <refname>SPI_execute_extended</refname> <refpurpose>execute a command with out-of-line parameters</refpurpose> </refnamediv> <refsynopsisdiv> <synopsis> -int SPI_execute_with_args(const char *<parameter>command</parameter>, - int <parameter>nargs</parameter>, Oid *<parameter>argtypes</parameter>, - Datum *<parameter>values</parameter>, const char *<parameter>nulls</parameter>, - bool <parameter>read_only</parameter>, long <parameter>count</parameter>) +int SPI_execute_extended(const char *<parameter>command</parameter>, + const SPIExecuteOptions * <parameter>options</parameter>) </synopsis> </refsynopsisdiv> @@ -658,30 +656,28 @@ int SPI_execute_with_args(const char *<parameter>command</parameter>, <title>Description</title> <para> - <function>SPI_execute_with_args</function> executes a command that might + <function>SPI_execute_extended</function> executes a command that might include references to externally supplied parameters. The command text - refers to a parameter as <literal>$<replaceable>n</replaceable></literal>, and - the call specifies data types and values for each such symbol. - <parameter>read_only</parameter> and <parameter>count</parameter> have - the same interpretation as in <function>SPI_execute</function>. + refers to a parameter as <literal>$<replaceable>n</replaceable></literal>, + and the <parameter>options->params</parameter> object (if supplied) + provides values and type information for each such symbol. + Various execution options can be specified + in the <parameter>options</parameter> struct, too. </para> <para> - The main advantage of this routine compared to - <function>SPI_execute</function> is that data values can be inserted - into the command without tedious quoting/escaping, and thus with much - less risk of SQL-injection attacks. + The <parameter>options->params</parameter> object should normally + mark each parameter with the <literal>PARAM_FLAG_CONST</literal> flag, + since a one-shot plan is always used for the query. </para> <para> - Similar results can be achieved with <function>SPI_prepare</function> followed by - <function>SPI_execute_plan</function>; however, when using this function - the query plan is always customized to the specific parameter values - provided. - For one-time query execution, this function should be preferred. - If the same command is to be executed with many different parameters, - either method might be faster, depending on the cost of re-planning - versus the benefit of custom plans. + If <parameter>options->dest</parameter> is not NULL, then result + tuples are passed to that object as they are generated by the executor, + instead of being accumulated in <varname>SPI_tuptable</varname>. Using + a caller-supplied <literal>DestReceiver</literal> object is particularly + helpful for queries that might generate many tuples, since the data can + be processed on-the-fly instead of being accumulated in memory. </para> </refsect1> @@ -699,69 +695,80 @@ int SPI_execute_with_args(const char *<parameter>command</parameter>, </varlistentry> <varlistentry> - <term><literal>int <parameter>nargs</parameter></literal></term> + <term><literal>const SPIExecuteOptions * <parameter>options</parameter></literal></term> <listitem> <para> - number of input parameters (<literal>$1</literal>, <literal>$2</literal>, etc.) + struct containing optional arguments </para> </listitem> </varlistentry> + </variablelist> + + <para> + Callers should always zero out the entire <parameter>options</parameter> + struct, then fill whichever fields they want to set. This ensures forward + compatibility of code, since any fields that are added to the struct in + future will be defined to behave backwards-compatibly if they are zero. + The currently available <parameter>options</parameter> fields are: + </para> + <variablelist> <varlistentry> - <term><literal>Oid * <parameter>argtypes</parameter></literal></term> + <term><literal>ParamListInfo <parameter>params</parameter></literal></term> <listitem> <para> - an array of length <parameter>nargs</parameter>, containing the - <acronym>OID</acronym>s of the data types of the parameters + data structure containing query parameter types and values; NULL if none </para> </listitem> </varlistentry> <varlistentry> - <term><literal>Datum * <parameter>values</parameter></literal></term> + <term><literal>bool <parameter>read_only</parameter></literal></term> <listitem> - <para> - an array of length <parameter>nargs</parameter>, containing the actual - parameter values - </para> + <para><literal>true</literal> for read-only execution</para> </listitem> </varlistentry> <varlistentry> - <term><literal>const char * <parameter>nulls</parameter></literal></term> + <term><literal>bool <parameter>no_snapshots</parameter></literal></term> <listitem> <para> - an array of length <parameter>nargs</parameter>, describing which - parameters are null + <literal>true</literal> prevents SPI from managing snapshots for + execution of the query; use with extreme caution </para> + </listitem> + </varlistentry> + <varlistentry> + <term><literal>uint64 <parameter>tcount</parameter></literal></term> + <listitem> <para> - If <parameter>nulls</parameter> is <symbol>NULL</symbol> then - <function>SPI_execute_with_args</function> assumes that no parameters - are null. Otherwise, each entry of the <parameter>nulls</parameter> - array should be <literal>' '</literal> if the corresponding parameter - value is non-null, or <literal>'n'</literal> if the corresponding parameter - value is null. (In the latter case, the actual value in the - corresponding <parameter>values</parameter> entry doesn't matter.) Note - that <parameter>nulls</parameter> is not a text string, just an array: - it does not need a <literal>'\0'</literal> terminator. + maximum number of rows to return, + or <literal>0</literal> for no limit </para> </listitem> </varlistentry> <varlistentry> - <term><literal>bool <parameter>read_only</parameter></literal></term> + <term><literal>DestReceiver * <parameter>dest</parameter></literal></term> <listitem> - <para><literal>true</literal> for read-only execution</para> + <para> + <literal>DestReceiver</literal> object that will receive any tuples + emitted by the query; if NULL, result tuples are accumulated into + a <varname>SPI_tuptable</varname> structure, as + in <function>SPI_execute</function> + </para> </listitem> </varlistentry> <varlistentry> - <term><literal>long <parameter>count</parameter></literal></term> + <term><literal>ResourceOwner <parameter>owner</parameter></literal></term> <listitem> <para> - maximum number of rows to return, - or <literal>0</literal> for no limit + This field is present for consistency + with <function>SPI_execute_plan_extended</function>, but it is + ignored, since the plan used + by <function>SPI_execute_extended</function> is never saved. </para> </listitem> </varlistentry> @@ -776,35 +783,40 @@ int SPI_execute_with_args(const char *<parameter>command</parameter>, </para> <para> + When <parameter>options->dest</parameter> is NULL, <varname>SPI_processed</varname> and <varname>SPI_tuptable</varname> are set as in - <function>SPI_execute</function> if successful. + <function>SPI_execute</function>. + When <parameter>options->dest</parameter> is not NULL, + <varname>SPI_processed</varname> is set to zero and + <varname>SPI_tuptable</varname> is set to NULL. If a tuple count + is required, the caller's <literal>DestReceiver</literal> object must + calculate it. </para> </refsect1> </refentry> <!-- *********************************************** --> -<refentry id="spi-spi-execute-with-receiver"> - <indexterm><primary>SPI_execute_with_receiver</primary></indexterm> +<refentry id="spi-spi-execute-with-args"> + <indexterm><primary>SPI_execute_with_args</primary></indexterm> <refmeta> - <refentrytitle>SPI_execute_with_receiver</refentrytitle> + <refentrytitle>SPI_execute_with_args</refentrytitle> <manvolnum>3</manvolnum> </refmeta> <refnamediv> - <refname>SPI_execute_with_receiver</refname> + <refname>SPI_execute_with_args</refname> <refpurpose>execute a command with out-of-line parameters</refpurpose> </refnamediv> <refsynopsisdiv> <synopsis> - int SPI_execute_with_receiver(const char *<parameter>command</parameter>, - ParamListInfo <parameter>params</parameter>, - bool <parameter>read_only</parameter>, - long <parameter>count</parameter>, - DestReceiver *<parameter>dest</parameter>) +int SPI_execute_with_args(const char *<parameter>command</parameter>, + int <parameter>nargs</parameter>, Oid *<parameter>argtypes</parameter>, + Datum *<parameter>values</parameter>, const char *<parameter>nulls</parameter>, + bool <parameter>read_only</parameter>, long <parameter>count</parameter>) </synopsis> </refsynopsisdiv> @@ -812,28 +824,30 @@ int SPI_execute_with_args(const char *<parameter>command</parameter>, <title>Description</title> <para> - <function>SPI_execute_with_receiver</function> executes a command that might + <function>SPI_execute_with_args</function> executes a command that might include references to externally supplied parameters. The command text - refers to a parameter as <literal>$<replaceable>n</replaceable></literal>, - and the <parameter>params</parameter> object provides values and type - information for each such symbol. + refers to a parameter as <literal>$<replaceable>n</replaceable></literal>, and + the call specifies data types and values for each such symbol. <parameter>read_only</parameter> and <parameter>count</parameter> have the same interpretation as in <function>SPI_execute</function>. </para> <para> - If <parameter>dest</parameter> is not NULL, then result tuples are passed - to that object as they are generated by the executor, instead of being - accumulated in <varname>SPI_tuptable</varname>. Using a - caller-supplied <literal>DestReceiver</literal> object is particularly - helpful for queries that might generate many tuples, since the data can - be processed on-the-fly instead of being accumulated in memory. + The main advantage of this routine compared to + <function>SPI_execute</function> is that data values can be inserted + into the command without tedious quoting/escaping, and thus with much + less risk of SQL-injection attacks. </para> <para> - The <parameter>params</parameter> object should normally mark each - parameter with the <literal>PARAM_FLAG_CONST</literal> flag, since - a one-shot plan is always used for the query. + Similar results can be achieved with <function>SPI_prepare</function> followed by + <function>SPI_execute_plan</function>; however, when using this function + the query plan is always customized to the specific parameter values + provided. + For one-time query execution, this function should be preferred. + If the same command is to be executed with many different parameters, + either method might be faster, depending on the cost of re-planning + versus the benefit of custom plans. </para> </refsect1> @@ -851,38 +865,69 @@ int SPI_execute_with_args(const char *<parameter>command</parameter>, </varlistentry> <varlistentry> - <term><literal>ParamListInfo <parameter>params</parameter></literal></term> + <term><literal>int <parameter>nargs</parameter></literal></term> <listitem> <para> - data structure containing parameter types and values; NULL if none + number of input parameters (<literal>$1</literal>, <literal>$2</literal>, etc.) </para> </listitem> </varlistentry> <varlistentry> - <term><literal>bool <parameter>read_only</parameter></literal></term> + <term><literal>Oid * <parameter>argtypes</parameter></literal></term> <listitem> - <para><literal>true</literal> for read-only execution</para> + <para> + an array of length <parameter>nargs</parameter>, containing the + <acronym>OID</acronym>s of the data types of the parameters + </para> </listitem> </varlistentry> <varlistentry> - <term><literal>long <parameter>count</parameter></literal></term> + <term><literal>Datum * <parameter>values</parameter></literal></term> <listitem> <para> - maximum number of rows to return, - or <literal>0</literal> for no limit + an array of length <parameter>nargs</parameter>, containing the actual + parameter values </para> </listitem> </varlistentry> <varlistentry> - <term><literal>DestReceiver * <parameter>dest</parameter></literal></term> + <term><literal>const char * <parameter>nulls</parameter></literal></term> <listitem> <para> - <literal>DestReceiver</literal> object that will receive any tuples - emitted by the query; if NULL, tuples are returned - in <varname>SPI_tuptable</varname> + an array of length <parameter>nargs</parameter>, describing which + parameters are null + </para> + + <para> + If <parameter>nulls</parameter> is <symbol>NULL</symbol> then + <function>SPI_execute_with_args</function> assumes that no parameters + are null. Otherwise, each entry of the <parameter>nulls</parameter> + array should be <literal>' '</literal> if the corresponding parameter + value is non-null, or <literal>'n'</literal> if the corresponding parameter + value is null. (In the latter case, the actual value in the + corresponding <parameter>values</parameter> entry doesn't matter.) Note + that <parameter>nulls</parameter> is not a text string, just an array: + it does not need a <literal>'\0'</literal> terminator. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>bool <parameter>read_only</parameter></literal></term> + <listitem> + <para><literal>true</literal> for read-only execution</para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>long <parameter>count</parameter></literal></term> + <listitem> + <para> + maximum number of rows to return, + or <literal>0</literal> for no limit </para> </listitem> </varlistentry> @@ -897,15 +942,9 @@ int SPI_execute_with_args(const char *<parameter>command</parameter>, </para> <para> - When <parameter>dest</parameter> is NULL, <varname>SPI_processed</varname> and <varname>SPI_tuptable</varname> are set as in - <function>SPI_execute</function>. - When <parameter>dest</parameter> is not NULL, - <varname>SPI_processed</varname> is set to zero and - <varname>SPI_tuptable</varname> is set to NULL. If a tuple count - is required, the caller's <literal>DestReceiver</literal> object must - calculate it. + <function>SPI_execute</function> if successful. </para> </refsect1> </refentry> @@ -1873,11 +1912,11 @@ int SPI_execute_plan_extended(SPIPlanPtr <parameter>plan</parameter>, </para> <para> - When <parameter>dest</parameter> is NULL, + When <parameter>options->dest</parameter> is NULL, <varname>SPI_processed</varname> and <varname>SPI_tuptable</varname> are set as in <function>SPI_execute_plan</function>. - When <parameter>dest</parameter> is not NULL, + When <parameter>options->dest</parameter> is not NULL, <varname>SPI_processed</varname> is set to zero and <varname>SPI_tuptable</varname> is set to NULL. If a tuple count is required, the caller's <literal>DestReceiver</literal> object must @@ -2263,6 +2302,12 @@ Portal SPI_cursor_open_with_args(const char *<parameter>name</parameter>, The passed-in parameter data will be copied into the cursor's portal, so it can be freed while the cursor still exists. </para> + + <para> + This function is now deprecated in favor + of <function>SPI_cursor_parse_open</function>, which provides equivalent + functionality using a more modern API for handling query parameters. + </para> </refsect1> <refsect1> @@ -2465,26 +2510,24 @@ Portal SPI_cursor_open_with_paramlist(const char *<parameter>name</parameter>, <!-- *********************************************** --> -<refentry id="spi-spi-cursor-parse-open-with-paramlist"> - <indexterm><primary>SPI_cursor_parse_open_with_paramlist</primary></indexterm> +<refentry id="spi-spi-cursor-parse-open"> + <indexterm><primary>SPI_cursor_parse_open</primary></indexterm> <refmeta> - <refentrytitle>SPI_cursor_parse_open_with_paramlist</refentrytitle> + <refentrytitle>SPI_cursor_parse_open</refentrytitle> <manvolnum>3</manvolnum> </refmeta> <refnamediv> - <refname>SPI_cursor_parse_open_with_paramlist</refname> - <refpurpose>set up a cursor using a query and parameters</refpurpose> + <refname>SPI_cursor_parse_open</refname> + <refpurpose>set up a cursor using a query string and parameters</refpurpose> </refnamediv> <refsynopsisdiv> <synopsis> -Portal SPI_cursor_parse_open_with_paramlist(const char *<parameter>name</parameter>, - const char *<parameter>command</parameter>, - ParamListInfo <parameter>params</parameter>, - bool <parameter>read_only</parameter>, - int <parameter>cursorOptions</parameter>) +Portal SPI_cursor_parse_open(const char *<parameter>name</parameter>, + const char *<parameter>command</parameter>, + const SPIParseOpenOptions * <parameter>options</parameter>) </synopsis> </refsynopsisdiv> @@ -2492,17 +2535,27 @@ Portal SPI_cursor_parse_open_with_paramlist(const char *<parameter>name</paramet <title>Description</title> <para> - <function>SPI_cursor_parse_open_with_paramlist</function> sets up a cursor - (internally, a portal) that will execute the specified query. This - function is equivalent to <function>SPI_cursor_open_with_args</function> - except that any parameters referenced by the query are provided by - a <literal>ParamListInfo</literal> object, rather than in ad-hoc arrays. + <function>SPI_cursor_parse_open</function> sets up a cursor + (internally, a portal) that will execute the specified query string. + This is comparable to <function>SPI_prepare_cursor</function> followed + by <function>SPI_cursor_open_with_paramlist</function>, except that + parameter references within the query string are handled entirely by + supplying a <literal>ParamListInfo</literal> object. + </para> + + <para> + For one-time query execution, this function should be preferred + over <function>SPI_prepare_cursor</function> followed by + <function>SPI_cursor_open_with_paramlist</function>. + If the same command is to be executed with many different parameters, + either method might be faster, depending on the cost of re-planning + versus the benefit of custom plans. </para> <para> - The <parameter>params</parameter> object should normally mark each - parameter with the <literal>PARAM_FLAG_CONST</literal> flag, since - a one-shot plan is always used for the query. + The <parameter>options->params</parameter> object should normally + mark each parameter with the <literal>PARAM_FLAG_CONST</literal> flag, + since a one-shot plan is always used for the query. </para> <para> @@ -2535,18 +2588,30 @@ Portal SPI_cursor_parse_open_with_paramlist(const char *<parameter>name</paramet </varlistentry> <varlistentry> - <term><literal>ParamListInfo <parameter>params</parameter></literal></term> + <term><literal>const SPIParseOpenOptions * <parameter>options</parameter></literal></term> <listitem> <para> - data structure containing parameter types and values; NULL if none + struct containing optional arguments </para> </listitem> </varlistentry> + </variablelist> + + <para> + Callers should always zero out the entire <parameter>options</parameter> + struct, then fill whichever fields they want to set. This ensures forward + compatibility of code, since any fields that are added to the struct in + future will be defined to behave backwards-compatibly if they are zero. + The currently available <parameter>options</parameter> fields are: + </para> + <variablelist> <varlistentry> - <term><literal>bool <parameter>read_only</parameter></literal></term> + <term><literal>ParamListInfo <parameter>params</parameter></literal></term> <listitem> - <para><literal>true</literal> for read-only execution</para> + <para> + data structure containing query parameter types and values; NULL if none + </para> </listitem> </varlistentry> @@ -2558,6 +2623,13 @@ Portal SPI_cursor_parse_open_with_paramlist(const char *<parameter>name</paramet </para> </listitem> </varlistentry> + + <varlistentry> + <term><literal>bool <parameter>read_only</parameter></literal></term> + <listitem> + <para><literal>true</literal> for read-only execution</para> + </listitem> + </varlistentry> </variablelist> </refsect1> |