Debian Codegraph @

doc/src/sgml/spi.sgml

raw
1 <!-- doc/src/sgml/spi.sgml -->
2
3 <chapter id="spi">
4 <title>Server Programming Interface</title>
5
6 <indexterm zone="spi">
7 <primary>SPI</primary>
8 </indexterm>
9
10 <para>
11 The <firstterm>Server Programming Interface</firstterm>
12 (<acronym>SPI</acronym>) gives writers of user-defined
13 <acronym>C</acronym> functions the ability to run
14 <acronym>SQL</acronym> commands inside their functions or procedures.
15 <acronym>SPI</acronym> is a set of
16 interface functions to simplify access to the parser, planner,
17 and executor. <acronym>SPI</acronym> also does some
18 memory management.
19 </para>
20
21 <note>
22 <para>
23 The available procedural languages provide various means to
24 execute SQL commands from functions. Most of these facilities are
25 based on SPI, so this documentation might be of use for users
26 of those languages as well.
27 </para>
28 </note>
29
30 <para>
31 Note that if a command invoked via SPI fails, then control will not be
32 returned to your C function. Rather, the
33 transaction or subtransaction in which your C function executes will be
34 rolled back. (This might seem surprising given that the SPI functions mostly
35 have documented error-return conventions. Those conventions only apply
36 for errors detected within the SPI functions themselves, however.)
37 It is possible to recover control after an error by establishing your own
38 subtransaction surrounding SPI calls that might fail.
39 </para>
40
41 <para>
42 <acronym>SPI</acronym> functions return a nonnegative result on
43 success (either via a returned integer value or in the global
44 variable <varname>SPI_result</varname>, as described below). On
45 error, a negative result or <symbol>NULL</symbol> will be returned.
46 </para>
47
48 <para>
49 Source code files that use SPI must include the header file
50 <filename>executor/spi.h</filename>.
51 </para>
52
53
54 <sect1 id="spi-interface">
55 <title>Interface Functions</title>
56
57 <refentry id="spi-spi-connect">
58 <indexterm><primary>SPI_connect</primary></indexterm>
59 <indexterm><primary>SPI_connect_ext</primary></indexterm>
60
61 <refmeta>
62 <refentrytitle>SPI_connect</refentrytitle>
63 <manvolnum>3</manvolnum>
64 </refmeta>
65
66 <refnamediv>
67 <refname>SPI_connect</refname>
68 <refname>SPI_connect_ext</refname>
69 <refpurpose>connect a C function to the SPI manager</refpurpose>
70 </refnamediv>
71
72 <refsynopsisdiv>
73 <synopsis>
74 int SPI_connect(void)
75 </synopsis>
76
77 <synopsis>
78 int SPI_connect_ext(int <parameter>options</parameter>)
79 </synopsis>
80 </refsynopsisdiv>
81
82 <refsect1>
83 <title>Description</title>
84
85 <para>
86 <function>SPI_connect</function> opens a connection from a
87 C function invocation to the SPI manager. You must call this
88 function if you want to execute commands through SPI. Some utility
89 SPI functions can be called from unconnected C functions.
90 </para>
91
92 <para>
93 <function>SPI_connect_ext</function> does the same but has an argument that
94 allows passing option flags. Currently, the following option values are
95 available:
96 <variablelist>
97 <varlistentry>
98 <term><symbol>SPI_OPT_NONATOMIC</symbol></term>
99 <listitem>
100 <para>
101 Sets the SPI connection to be <firstterm>nonatomic</firstterm>, which
102 means that transaction control calls (<function>SPI_commit</function>,
103 <function>SPI_rollback</function>) are allowed. Otherwise,
104 calling those functions will result in an immediate error.
105 </para>
106 </listitem>
107 </varlistentry>
108 </variablelist>
109 </para>
110
111 <para>
112 <literal>SPI_connect()</literal> is equivalent to
113 <literal>SPI_connect_ext(0)</literal>.
114 </para>
115 </refsect1>
116
117 <refsect1>
118 <title>Return Value</title>
119
120 <variablelist>
121 <varlistentry>
122 <term><symbol>SPI_OK_CONNECT</symbol></term>
123 <listitem>
124 <para>
125 on success
126 </para>
127 </listitem>
128 </varlistentry>
129 </variablelist>
130
131 <para>
132 The fact that these functions return <type>int</type>
133 not <type>void</type> is historical. All failure cases are reported
134 via <function>ereport</function> or <function>elog</function>.
135 (In versions before <productname>PostgreSQL</productname> v10,
136 some but not all failures would be reported with a result value
137 of <symbol>SPI_ERROR_CONNECT</symbol>.)
138 </para>
139 </refsect1>
140 </refentry>
141
142 <!-- *********************************************** -->
143
144 <refentry id="spi-spi-finish">
145 <indexterm><primary>SPI_finish</primary></indexterm>
146
147 <refmeta>
148 <refentrytitle>SPI_finish</refentrytitle>
149 <manvolnum>3</manvolnum>
150 </refmeta>
151
152 <refnamediv>
153 <refname>SPI_finish</refname>
154 <refpurpose>disconnect a C function from the SPI manager</refpurpose>
155 </refnamediv>
156
157 <refsynopsisdiv>
158 <synopsis>
159 int SPI_finish(void)
160 </synopsis>
161 </refsynopsisdiv>
162
163 <refsect1>
164 <title>Description</title>
165
166 <para>
167 <function>SPI_finish</function> closes an existing connection to
168 the SPI manager. You must call this function after completing the
169 SPI operations needed during your C function's current invocation.
170 You do not need to worry about making this happen, however, if you
171 abort the transaction via <literal>elog(ERROR)</literal>. In that
172 case SPI will clean itself up automatically.
173 </para>
174 </refsect1>
175
176 <refsect1>
177 <title>Return Value</title>
178
179 <variablelist>
180 <varlistentry>
181 <term><symbol>SPI_OK_FINISH</symbol></term>
182 <listitem>
183 <para>
184 if properly disconnected
185 </para>
186 </listitem>
187 </varlistentry>
188
189 <varlistentry>
190 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
191 <listitem>
192 <para>
193 if called from an unconnected C function
194 </para>
195 </listitem>
196 </varlistentry>
197 </variablelist>
198 </refsect1>
199 </refentry>
200
201 <!-- *********************************************** -->
202
203 <refentry id="spi-spi-execute">
204 <indexterm><primary>SPI_execute</primary></indexterm>
205
206 <refmeta>
207 <refentrytitle>SPI_execute</refentrytitle>
208 <manvolnum>3</manvolnum>
209 </refmeta>
210
211 <refnamediv>
212 <refname>SPI_execute</refname>
213 <refpurpose>execute a command</refpurpose>
214 </refnamediv>
215
216 <refsynopsisdiv>
217 <synopsis>
218 int SPI_execute(const char * <parameter>command</parameter>, bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
219 </synopsis>
220 </refsynopsisdiv>
221
222 <refsect1>
223 <title>Description</title>
224
225 <para>
226 <function>SPI_execute</function> executes the specified SQL command
227 for <parameter>count</parameter> rows. If <parameter>read_only</parameter>
228 is <literal>true</literal>, the command must be read-only, and execution overhead
229 is somewhat reduced.
230 </para>
231
232 <para>
233 This function can only be called from a connected C function.
234 </para>
235
236 <para>
237 If <parameter>count</parameter> is zero then the command is executed
238 for all rows that it applies to. If <parameter>count</parameter>
239 is greater than zero, then no more than <parameter>count</parameter> rows
240 will be retrieved; execution stops when the count is reached, much like
241 adding a <literal>LIMIT</literal> clause to the query. For example,
242 <programlisting>
243 SPI_execute("SELECT * FROM foo", true, 5);
244 </programlisting>
245 will retrieve at most 5 rows from the table. Note that such a limit
246 is only effective when the command actually returns rows. For example,
247 <programlisting>
248 SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
249 </programlisting>
250 inserts all rows from <structname>bar</structname>, ignoring the
251 <parameter>count</parameter> parameter. However, with
252 <programlisting>
253 SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);
254 </programlisting>
255 at most 5 rows would be inserted, since execution would stop after the
256 fifth <literal>RETURNING</literal> result row is retrieved.
257 </para>
258
259 <para>
260 You can pass multiple commands in one string;
261 <function>SPI_execute</function> returns the
262 result for the command executed last. The <parameter>count</parameter>
263 limit applies to each command separately (even though only the last
264 result will actually be returned). The limit is not applied to any
265 hidden commands generated by rules.
266 </para>
267
268 <para>
269 When <parameter>read_only</parameter> is <literal>false</literal>,
270 <function>SPI_execute</function> increments the command
271 counter and computes a new <firstterm>snapshot</firstterm> before executing each
272 command in the string. The snapshot does not actually change if the
273 current transaction isolation level is <literal>SERIALIZABLE</literal> or <literal>REPEATABLE READ</literal>, but in
274 <literal>READ COMMITTED</literal> mode the snapshot update allows each command to
275 see the results of newly committed transactions from other sessions.
276 This is essential for consistent behavior when the commands are modifying
277 the database.
278 </para>
279
280 <para>
281 When <parameter>read_only</parameter> is <literal>true</literal>,
282 <function>SPI_execute</function> does not update either the snapshot
283 or the command counter, and it allows only plain <command>SELECT</command>
284 commands to appear in the command string. The commands are executed
285 using the snapshot previously established for the surrounding query.
286 This execution mode is somewhat faster than the read/write mode due
287 to eliminating per-command overhead. It also allows genuinely
288 <firstterm>stable</firstterm> functions to be built: since successive executions
289 will all use the same snapshot, there will be no change in the results.
290 </para>
291
292 <para>
293 It is generally unwise to mix read-only and read-write commands within
294 a single function using SPI; that could result in very confusing behavior,
295 since the read-only queries would not see the results of any database
296 updates done by the read-write queries.
297 </para>
298
299 <para>
300 The actual number of rows for which the (last) command was executed
301 is returned in the global variable <varname>SPI_processed</varname>.
302 If the return value of the function is <symbol>SPI_OK_SELECT</symbol>,
303 <symbol>SPI_OK_INSERT_RETURNING</symbol>,
304 <symbol>SPI_OK_DELETE_RETURNING</symbol>,
305 <symbol>SPI_OK_UPDATE_RETURNING</symbol>, or
306 <symbol>SPI_OK_MERGE_RETURNING</symbol>,
307 then you can use the
308 global pointer <literal>SPITupleTable *SPI_tuptable</literal> to
309 access the result rows. Some utility commands (such as
310 <command>EXPLAIN</command>) also return row sets, and <literal>SPI_tuptable</literal>
311 will contain the result in these cases too. Some utility commands
312 (<command>COPY</command>, <command>CREATE TABLE AS</command>) don't return a row set, so
313 <literal>SPI_tuptable</literal> is NULL, but they still return the number of
314 rows processed in <varname>SPI_processed</varname>.
315 </para>
316
317 <para>
318 The structure <structname>SPITupleTable</structname> is defined
319 thus:
320 <programlisting>
321 typedef struct SPITupleTable
322 {
323 /* Public members */
324 TupleDesc tupdesc; /* tuple descriptor */
325 HeapTuple *vals; /* array of tuples */
326 uint64 numvals; /* number of valid tuples */
327
328 /* Private members, not intended for external callers */
329 uint64 alloced; /* allocated length of vals array */
330 MemoryContext tuptabcxt; /* memory context of result table */
331 slist_node next; /* link for internal bookkeeping */
332 SubTransactionId subid; /* subxact in which tuptable was created */
333 } SPITupleTable;
334 </programlisting>
335 The fields <structfield>tupdesc</structfield>,
336 <structfield>vals</structfield>, and
337 <structfield>numvals</structfield>
338 can be used by SPI callers; the remaining fields are internal.
339 <structfield>vals</structfield> is an array of pointers to rows.
340 The number of rows is given by <structfield>numvals</structfield>
341 (for somewhat historical reasons, this count is also returned
342 in <varname>SPI_processed</varname>).
343 <structfield>tupdesc</structfield> is a row descriptor which you can pass to
344 SPI functions dealing with rows.
345 </para>
346
347 <para>
348 <function>SPI_finish</function> frees all
349 <structname>SPITupleTable</structname>s allocated during the current
350 C function. You can free a particular result table earlier, if you
351 are done with it, by calling <function>SPI_freetuptable</function>.
352 </para>
353 </refsect1>
354
355 <refsect1>
356 <title>Arguments</title>
357
358 <variablelist>
359 <varlistentry>
360 <term><literal>const char * <parameter>command</parameter></literal></term>
361 <listitem>
362 <para>
363 string containing command to execute
364 </para>
365 </listitem>
366 </varlistentry>
367
368 <varlistentry>
369 <term><literal>bool <parameter>read_only</parameter></literal></term>
370 <listitem>
371 <para><literal>true</literal> for read-only execution</para>
372 </listitem>
373 </varlistentry>
374
375 <varlistentry>
376 <term><literal>long <parameter>count</parameter></literal></term>
377 <listitem>
378 <para>
379 maximum number of rows to return,
380 or <literal>0</literal> for no limit
381 </para>
382 </listitem>
383 </varlistentry>
384 </variablelist>
385 </refsect1>
386
387 <refsect1>
388 <title>Return Value</title>
389
390 <para>
391 If the execution of the command was successful then one of the
392 following (nonnegative) values will be returned:
393
394 <variablelist>
395 <varlistentry>
396 <term><symbol>SPI_OK_SELECT</symbol></term>
397 <listitem>
398 <para>
399 if a <command>SELECT</command> (but not <command>SELECT
400 INTO</command>) was executed
401 </para>
402 </listitem>
403 </varlistentry>
404
405 <varlistentry>
406 <term><symbol>SPI_OK_SELINTO</symbol></term>
407 <listitem>
408 <para>
409 if a <command>SELECT INTO</command> was executed
410 </para>
411 </listitem>
412 </varlistentry>
413
414 <varlistentry>
415 <term><symbol>SPI_OK_INSERT</symbol></term>
416 <listitem>
417 <para>
418 if an <command>INSERT</command> was executed
419 </para>
420 </listitem>
421 </varlistentry>
422
423 <varlistentry>
424 <term><symbol>SPI_OK_DELETE</symbol></term>
425 <listitem>
426 <para>
427 if a <command>DELETE</command> was executed
428 </para>
429 </listitem>
430 </varlistentry>
431
432 <varlistentry>
433 <term><symbol>SPI_OK_UPDATE</symbol></term>
434 <listitem>
435 <para>
436 if an <command>UPDATE</command> was executed
437 </para>
438 </listitem>
439 </varlistentry>
440
441 <varlistentry>
442 <term><symbol>SPI_OK_MERGE</symbol></term>
443 <listitem>
444 <para>
445 if a <command>MERGE</command> was executed
446 </para>
447 </listitem>
448 </varlistentry>
449
450 <varlistentry>
451 <term><symbol>SPI_OK_INSERT_RETURNING</symbol></term>
452 <listitem>
453 <para>
454 if an <command>INSERT RETURNING</command> was executed
455 </para>
456 </listitem>
457 </varlistentry>
458
459 <varlistentry>
460 <term><symbol>SPI_OK_DELETE_RETURNING</symbol></term>
461 <listitem>
462 <para>
463 if a <command>DELETE RETURNING</command> was executed
464 </para>
465 </listitem>
466 </varlistentry>
467
468 <varlistentry>
469 <term><symbol>SPI_OK_UPDATE_RETURNING</symbol></term>
470 <listitem>
471 <para>
472 if an <command>UPDATE RETURNING</command> was executed
473 </para>
474 </listitem>
475 </varlistentry>
476
477 <varlistentry>
478 <term><symbol>SPI_OK_MERGE_RETURNING</symbol></term>
479 <listitem>
480 <para>
481 if a <command>MERGE RETURNING</command> was executed
482 </para>
483 </listitem>
484 </varlistentry>
485
486 <varlistentry>
487 <term><symbol>SPI_OK_UTILITY</symbol></term>
488 <listitem>
489 <para>
490 if a utility command (e.g., <command>CREATE TABLE</command>)
491 was executed
492 </para>
493 </listitem>
494 </varlistentry>
495
496 <varlistentry>
497 <term><symbol>SPI_OK_REWRITTEN</symbol></term>
498 <listitem>
499 <para>
500 if the command was rewritten into another kind of command (e.g.,
501 <command>UPDATE</command> became an <command>INSERT</command>) by a <link linkend="rules">rule</link>.
502 </para>
503 </listitem>
504 </varlistentry>
505 </variablelist>
506 </para>
507
508 <para>
509 On error, one of the following negative values is returned:
510
511 <variablelist>
512 <varlistentry>
513 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
514 <listitem>
515 <para>
516 if <parameter>command</parameter> is <symbol>NULL</symbol> or
517 <parameter>count</parameter> is less than 0
518 </para>
519 </listitem>
520 </varlistentry>
521
522 <varlistentry>
523 <term><symbol>SPI_ERROR_COPY</symbol></term>
524 <listitem>
525 <para>
526 if <command>COPY TO stdout</command> or <command>COPY FROM stdin</command>
527 was attempted
528 </para>
529 </listitem>
530 </varlistentry>
531
532 <varlistentry>
533 <term><symbol>SPI_ERROR_TRANSACTION</symbol></term>
534 <listitem>
535 <para>
536 if a transaction manipulation command was attempted
537 (<command>BEGIN</command>,
538 <command>COMMIT</command>,
539 <command>ROLLBACK</command>,
540 <command>SAVEPOINT</command>,
541 <command>PREPARE TRANSACTION</command>,
542 <command>COMMIT PREPARED</command>,
543 <command>ROLLBACK PREPARED</command>,
544 or any variant thereof)
545 </para>
546 </listitem>
547 </varlistentry>
548
549 <varlistentry>
550 <term><symbol>SPI_ERROR_OPUNKNOWN</symbol></term>
551 <listitem>
552 <para>
553 if the command type is unknown (shouldn't happen)
554 </para>
555 </listitem>
556 </varlistentry>
557
558 <varlistentry>
559 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
560 <listitem>
561 <para>
562 if called from an unconnected C function
563 </para>
564 </listitem>
565 </varlistentry>
566 </variablelist>
567 </para>
568 </refsect1>
569
570 <refsect1>
571 <title>Notes</title>
572
573 <para>
574 All SPI query-execution functions set both
575 <varname>SPI_processed</varname> and
576 <varname>SPI_tuptable</varname> (just the pointer, not the contents
577 of the structure). Save these two global variables into local
578 C function variables if you need to access the result table of
579 <function>SPI_execute</function> or another query-execution function
580 across later calls.
581 </para>
582 </refsect1>
583 </refentry>
584
585 <!-- *********************************************** -->
586
587 <refentry id="spi-spi-exec">
588 <indexterm><primary>SPI_exec</primary></indexterm>
589
590 <refmeta>
591 <refentrytitle>SPI_exec</refentrytitle>
592 <manvolnum>3</manvolnum>
593 </refmeta>
594
595 <refnamediv>
596 <refname>SPI_exec</refname>
597 <refpurpose>execute a read/write command</refpurpose>
598 </refnamediv>
599
600 <refsynopsisdiv>
601 <synopsis>
602 int SPI_exec(const char * <parameter>command</parameter>, long <parameter>count</parameter>)
603 </synopsis>
604 </refsynopsisdiv>
605
606 <refsect1>
607 <title>Description</title>
608
609 <para>
610 <function>SPI_exec</function> is the same as
611 <function>SPI_execute</function>, with the latter's
612 <parameter>read_only</parameter> parameter always taken as
613 <literal>false</literal>.
614 </para>
615 </refsect1>
616
617 <refsect1>
618 <title>Arguments</title>
619
620 <variablelist>
621 <varlistentry>
622 <term><literal>const char * <parameter>command</parameter></literal></term>
623 <listitem>
624 <para>
625 string containing command to execute
626 </para>
627 </listitem>
628 </varlistentry>
629
630 <varlistentry>
631 <term><literal>long <parameter>count</parameter></literal></term>
632 <listitem>
633 <para>
634 maximum number of rows to return,
635 or <literal>0</literal> for no limit
636 </para>
637 </listitem>
638 </varlistentry>
639 </variablelist>
640 </refsect1>
641
642 <refsect1>
643 <title>Return Value</title>
644
645 <para>
646 See <function>SPI_execute</function>.
647 </para>
648 </refsect1>
649 </refentry>
650
651 <!-- *********************************************** -->
652
653 <refentry id="spi-spi-execute-extended">
654 <indexterm><primary>SPI_execute_extended</primary></indexterm>
655
656 <refmeta>
657 <refentrytitle>SPI_execute_extended</refentrytitle>
658 <manvolnum>3</manvolnum>
659 </refmeta>
660
661 <refnamediv>
662 <refname>SPI_execute_extended</refname>
663 <refpurpose>execute a command with out-of-line parameters</refpurpose>
664 </refnamediv>
665
666 <refsynopsisdiv>
667 <synopsis>
668 int SPI_execute_extended(const char *<parameter>command</parameter>,
669 const SPIExecuteOptions * <parameter>options</parameter>)
670 </synopsis>
671 </refsynopsisdiv>
672
673 <refsect1>
674 <title>Description</title>
675
676 <para>
677 <function>SPI_execute_extended</function> executes a command that might
678 include references to externally supplied parameters. The command text
679 refers to a parameter as <literal>$<replaceable>n</replaceable></literal>,
680 and the <parameter>options-&gt;params</parameter> object (if supplied)
681 provides values and type information for each such symbol.
682 Various execution options can be specified
683 in the <parameter>options</parameter> struct, too.
684 </para>
685
686 <para>
687 The <parameter>options-&gt;params</parameter> object should normally
688 mark each parameter with the <literal>PARAM_FLAG_CONST</literal> flag,
689 since a one-shot plan is always used for the query.
690 </para>
691
692 <para>
693 If <parameter>options-&gt;dest</parameter> is not NULL, then result
694 tuples are passed to that object as they are generated by the executor,
695 instead of being accumulated in <varname>SPI_tuptable</varname>. Using
696 a caller-supplied <literal>DestReceiver</literal> object is particularly
697 helpful for queries that might generate many tuples, since the data can
698 be processed on-the-fly instead of being accumulated in memory.
699 </para>
700 </refsect1>
701
702 <refsect1>
703 <title>Arguments</title>
704
705 <variablelist>
706 <varlistentry>
707 <term><literal>const char * <parameter>command</parameter></literal></term>
708 <listitem>
709 <para>
710 command string
711 </para>
712 </listitem>
713 </varlistentry>
714
715 <varlistentry>
716 <term><literal>const SPIExecuteOptions * <parameter>options</parameter></literal></term>
717 <listitem>
718 <para>
719 struct containing optional arguments
720 </para>
721 </listitem>
722 </varlistentry>
723 </variablelist>
724
725 <para>
726 Callers should always zero out the entire <parameter>options</parameter>
727 struct, then fill whichever fields they want to set. This ensures forward
728 compatibility of code, since any fields that are added to the struct in
729 future will be defined to behave backwards-compatibly if they are zero.
730 The currently available <parameter>options</parameter> fields are:
731 </para>
732
733 <variablelist>
734 <varlistentry>
735 <term><literal>ParamListInfo <parameter>params</parameter></literal></term>
736 <listitem>
737 <para>
738 data structure containing query parameter types and values; NULL if none
739 </para>
740 </listitem>
741 </varlistentry>
742
743 <varlistentry>
744 <term><literal>bool <parameter>read_only</parameter></literal></term>
745 <listitem>
746 <para><literal>true</literal> for read-only execution</para>
747 </listitem>
748 </varlistentry>
749
750 <varlistentry>
751 <term><literal>bool <parameter>allow_nonatomic</parameter></literal></term>
752 <listitem>
753 <para>
754 <literal>true</literal> allows non-atomic execution of CALL and DO
755 statements (but this field is ignored unless
756 the <symbol>SPI_OPT_NONATOMIC</symbol> flag was passed
757 to <function>SPI_connect_ext</function>)
758 </para>
759 </listitem>
760 </varlistentry>
761
762 <varlistentry>
763 <term><literal>bool <parameter>must_return_tuples</parameter></literal></term>
764 <listitem>
765 <para>
766 if <literal>true</literal>, raise error if the query is not of a kind
767 that returns tuples (this does not forbid the case where it happens to
768 return zero tuples)
769 </para>
770 </listitem>
771 </varlistentry>
772
773 <varlistentry>
774 <term><literal>uint64 <parameter>tcount</parameter></literal></term>
775 <listitem>
776 <para>
777 maximum number of rows to return,
778 or <literal>0</literal> for no limit
779 </para>
780 </listitem>
781 </varlistentry>
782
783 <varlistentry>
784 <term><literal>DestReceiver * <parameter>dest</parameter></literal></term>
785 <listitem>
786 <para>
787 <literal>DestReceiver</literal> object that will receive any tuples
788 emitted by the query; if NULL, result tuples are accumulated into
789 a <varname>SPI_tuptable</varname> structure, as
790 in <function>SPI_execute</function>
791 </para>
792 </listitem>
793 </varlistentry>
794
795 <varlistentry>
796 <term><literal>ResourceOwner <parameter>owner</parameter></literal></term>
797 <listitem>
798 <para>
799 This field is present for consistency
800 with <function>SPI_execute_plan_extended</function>, but it is
801 ignored, since the plan used
802 by <function>SPI_execute_extended</function> is never saved.
803 </para>
804 </listitem>
805 </varlistentry>
806 </variablelist>
807 </refsect1>
808
809 <refsect1>
810 <title>Return Value</title>
811
812 <para>
813 The return value is the same as for <function>SPI_execute</function>.
814 </para>
815
816 <para>
817 When <parameter>options-&gt;dest</parameter> is NULL,
818 <varname>SPI_processed</varname> and
819 <varname>SPI_tuptable</varname> are set as in
820 <function>SPI_execute</function>.
821 When <parameter>options-&gt;dest</parameter> is not NULL,
822 <varname>SPI_processed</varname> is set to zero and
823 <varname>SPI_tuptable</varname> is set to NULL. If a tuple count
824 is required, the caller's <literal>DestReceiver</literal> object must
825 calculate it.
826 </para>
827 </refsect1>
828 </refentry>
829
830 <!-- *********************************************** -->
831
832 <refentry id="spi-spi-execute-with-args">
833 <indexterm><primary>SPI_execute_with_args</primary></indexterm>
834
835 <refmeta>
836 <refentrytitle>SPI_execute_with_args</refentrytitle>
837 <manvolnum>3</manvolnum>
838 </refmeta>
839
840 <refnamediv>
841 <refname>SPI_execute_with_args</refname>
842 <refpurpose>execute a command with out-of-line parameters</refpurpose>
843 </refnamediv>
844
845 <refsynopsisdiv>
846 <synopsis>
847 int SPI_execute_with_args(const char *<parameter>command</parameter>,
848 int <parameter>nargs</parameter>, Oid *<parameter>argtypes</parameter>,
849 Datum *<parameter>values</parameter>, const char *<parameter>nulls</parameter>,
850 bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
851 </synopsis>
852 </refsynopsisdiv>
853
854 <refsect1>
855 <title>Description</title>
856
857 <para>
858 <function>SPI_execute_with_args</function> executes a command that might
859 include references to externally supplied parameters. The command text
860 refers to a parameter as <literal>$<replaceable>n</replaceable></literal>, and
861 the call specifies data types and values for each such symbol.
862 <parameter>read_only</parameter> and <parameter>count</parameter> have
863 the same interpretation as in <function>SPI_execute</function>.
864 </para>
865
866 <para>
867 The main advantage of this routine compared to
868 <function>SPI_execute</function> is that data values can be inserted
869 into the command without tedious quoting/escaping, and thus with much
870 less risk of SQL-injection attacks.
871 </para>
872
873 <para>
874 Similar results can be achieved with <function>SPI_prepare</function> followed by
875 <function>SPI_execute_plan</function>; however, when using this function
876 the query plan is always customized to the specific parameter values
877 provided.
878 For one-time query execution, this function should be preferred.
879 If the same command is to be executed with many different parameters,
880 either method might be faster, depending on the cost of re-planning
881 versus the benefit of custom plans.
882 </para>
883 </refsect1>
884
885 <refsect1>
886 <title>Arguments</title>
887
888 <variablelist>
889 <varlistentry>
890 <term><literal>const char * <parameter>command</parameter></literal></term>
891 <listitem>
892 <para>
893 command string
894 </para>
895 </listitem>
896 </varlistentry>
897
898 <varlistentry>
899 <term><literal>int <parameter>nargs</parameter></literal></term>
900 <listitem>
901 <para>
902 number of input parameters (<literal>$1</literal>, <literal>$2</literal>, etc.)
903 </para>
904 </listitem>
905 </varlistentry>
906
907 <varlistentry>
908 <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
909 <listitem>
910 <para>
911 an array of length <parameter>nargs</parameter>, containing the
912 <acronym>OID</acronym>s of the data types of the parameters
913 </para>
914 </listitem>
915 </varlistentry>
916
917 <varlistentry>
918 <term><literal>Datum * <parameter>values</parameter></literal></term>
919 <listitem>
920 <para>
921 an array of length <parameter>nargs</parameter>, containing the actual
922 parameter values
923 </para>
924 </listitem>
925 </varlistentry>
926
927 <varlistentry>
928 <term><literal>const char * <parameter>nulls</parameter></literal></term>
929 <listitem>
930 <para>
931 an array of length <parameter>nargs</parameter>, describing which
932 parameters are null
933 </para>
934
935 <para>
936 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
937 <function>SPI_execute_with_args</function> assumes that no parameters
938 are null. Otherwise, each entry of the <parameter>nulls</parameter>
939 array should be <literal>'&nbsp;'</literal> if the corresponding parameter
940 value is non-null, or <literal>'n'</literal> if the corresponding parameter
941 value is null. (In the latter case, the actual value in the
942 corresponding <parameter>values</parameter> entry doesn't matter.) Note
943 that <parameter>nulls</parameter> is not a text string, just an array:
944 it does not need a <literal>'\0'</literal> terminator.
945 </para>
946 </listitem>
947 </varlistentry>
948
949 <varlistentry>
950 <term><literal>bool <parameter>read_only</parameter></literal></term>
951 <listitem>
952 <para><literal>true</literal> for read-only execution</para>
953 </listitem>
954 </varlistentry>
955
956 <varlistentry>
957 <term><literal>long <parameter>count</parameter></literal></term>
958 <listitem>
959 <para>
960 maximum number of rows to return,
961 or <literal>0</literal> for no limit
962 </para>
963 </listitem>
964 </varlistentry>
965 </variablelist>
966 </refsect1>
967
968 <refsect1>
969 <title>Return Value</title>
970
971 <para>
972 The return value is the same as for <function>SPI_execute</function>.
973 </para>
974
975 <para>
976 <varname>SPI_processed</varname> and
977 <varname>SPI_tuptable</varname> are set as in
978 <function>SPI_execute</function> if successful.
979 </para>
980 </refsect1>
981 </refentry>
982
983 <!-- *********************************************** -->
984
985 <refentry id="spi-spi-prepare">
986 <indexterm><primary>SPI_prepare</primary></indexterm>
987
988 <refmeta>
989 <refentrytitle>SPI_prepare</refentrytitle>
990 <manvolnum>3</manvolnum>
991 </refmeta>
992
993 <refnamediv>
994 <refname>SPI_prepare</refname>
995 <refpurpose>prepare a statement, without executing it yet</refpurpose>
996 </refnamediv>
997
998 <refsynopsisdiv>
999 <synopsis>
1000 SPIPlanPtr SPI_prepare(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>)
1001 </synopsis>
1002 </refsynopsisdiv>
1003
1004 <refsect1>
1005 <title>Description</title>
1006
1007 <para>
1008 <function>SPI_prepare</function> creates and returns a prepared
1009 statement for the specified command, but doesn't execute the command.
1010 The prepared statement can later be executed repeatedly using
1011 <function>SPI_execute_plan</function>.
1012 </para>
1013
1014 <para>
1015 When the same or a similar command is to be executed repeatedly, it
1016 is generally advantageous to perform parse analysis only once, and
1017 might furthermore be advantageous to re-use an execution plan for the
1018 command.
1019 <function>SPI_prepare</function> converts a command string into a
1020 prepared statement that encapsulates the results of parse analysis.
1021 The prepared statement also provides a place for caching an execution plan
1022 if it is found that generating a custom plan for each execution is not
1023 helpful.
1024 </para>
1025
1026 <para>
1027 A prepared command can be generalized by writing parameters
1028 (<literal>$1</literal>, <literal>$2</literal>, etc.) in place of what would be
1029 constants in a normal command. The actual values of the parameters
1030 are then specified when <function>SPI_execute_plan</function> is called.
1031 This allows the prepared command to be used over a wider range of
1032 situations than would be possible without parameters.
1033 </para>
1034
1035 <para>
1036 The statement returned by <function>SPI_prepare</function> can be used
1037 only in the current invocation of the C function, since
1038 <function>SPI_finish</function> frees memory allocated for such a
1039 statement. But the statement can be saved for longer using the functions
1040 <function>SPI_keepplan</function> or <function>SPI_saveplan</function>.
1041 </para>
1042 </refsect1>
1043
1044 <refsect1>
1045 <title>Arguments</title>
1046
1047 <variablelist>
1048 <varlistentry>
1049 <term><literal>const char * <parameter>command</parameter></literal></term>
1050 <listitem>
1051 <para>
1052 command string
1053 </para>
1054 </listitem>
1055 </varlistentry>
1056
1057 <varlistentry>
1058 <term><literal>int <parameter>nargs</parameter></literal></term>
1059 <listitem>
1060 <para>
1061 number of input parameters (<literal>$1</literal>, <literal>$2</literal>, etc.)
1062 </para>
1063 </listitem>
1064 </varlistentry>
1065
1066 <varlistentry>
1067 <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
1068 <listitem>
1069 <para>
1070 pointer to an array containing the <acronym>OID</acronym>s of
1071 the data types of the parameters
1072 </para>
1073 </listitem>
1074 </varlistentry>
1075 </variablelist>
1076 </refsect1>
1077
1078 <refsect1>
1079 <title>Return Value</title>
1080
1081 <para>
1082 <function>SPI_prepare</function> returns a non-null pointer to an
1083 <type>SPIPlan</type>, which is an opaque struct representing a prepared
1084 statement. On error, <symbol>NULL</symbol> will be returned,
1085 and <varname>SPI_result</varname> will be set to one of the same
1086 error codes used by <function>SPI_execute</function>, except that
1087 it is set to <symbol>SPI_ERROR_ARGUMENT</symbol> if
1088 <parameter>command</parameter> is <symbol>NULL</symbol>, or if
1089 <parameter>nargs</parameter> is less than 0, or if <parameter>nargs</parameter> is
1090 greater than 0 and <parameter>argtypes</parameter> is <symbol>NULL</symbol>.
1091 </para>
1092 </refsect1>
1093
1094 <refsect1>
1095 <title>Notes</title>
1096
1097 <para>
1098 If no parameters are defined, a generic plan will be created at the
1099 first use of <function>SPI_execute_plan</function>, and used for all
1100 subsequent executions as well. If there are parameters, the first few uses
1101 of <function>SPI_execute_plan</function> will generate custom plans
1102 that are specific to the supplied parameter values. After enough uses
1103 of the same prepared statement, <function>SPI_execute_plan</function> will
1104 build a generic plan, and if that is not too much more expensive than the
1105 custom plans, it will start using the generic plan instead of re-planning
1106 each time. If this default behavior is unsuitable, you can alter it by
1107 passing the <literal>CURSOR_OPT_GENERIC_PLAN</literal> or
1108 <literal>CURSOR_OPT_CUSTOM_PLAN</literal> flag to
1109 <function>SPI_prepare_cursor</function>, to force use of generic or custom
1110 plans respectively.
1111 </para>
1112
1113 <para>
1114 Although the main point of a prepared statement is to avoid repeated parse
1115 analysis and planning of the statement, <productname>PostgreSQL</productname> will
1116 force re-analysis and re-planning of the statement before using it
1117 whenever database objects used in the statement have undergone
1118 definitional (DDL) changes since the previous use of the prepared
1119 statement. Also, if the value of <xref linkend="guc-search-path"/> changes
1120 from one use to the next, the statement will be re-parsed using the new
1121 <varname>search_path</varname>. (This latter behavior is new as of
1122 <productname>PostgreSQL</productname> 9.3.) See <xref
1123 linkend="sql-prepare"/> for more information about the behavior of prepared
1124 statements.
1125 </para>
1126
1127 <para>
1128 This function should only be called from a connected C function.
1129 </para>
1130
1131 <para>
1132 <type>SPIPlanPtr</type> is declared as a pointer to an opaque struct type in
1133 <filename>spi.h</filename>. It is unwise to try to access its contents
1134 directly, as that makes your code much more likely to break in
1135 future revisions of <productname>PostgreSQL</productname>.
1136 </para>
1137
1138 <para>
1139 The name <type>SPIPlanPtr</type> is somewhat historical, since the data
1140 structure no longer necessarily contains an execution plan.
1141 </para>
1142 </refsect1>
1143 </refentry>
1144
1145 <!-- *********************************************** -->
1146
1147 <refentry id="spi-spi-prepare-cursor">
1148 <indexterm><primary>SPI_prepare_cursor</primary></indexterm>
1149
1150 <refmeta>
1151 <refentrytitle>SPI_prepare_cursor</refentrytitle>
1152 <manvolnum>3</manvolnum>
1153 </refmeta>
1154
1155 <refnamediv>
1156 <refname>SPI_prepare_cursor</refname>
1157 <refpurpose>prepare a statement, without executing it yet</refpurpose>
1158 </refnamediv>
1159
1160 <refsynopsisdiv>
1161 <synopsis>
1162 SPIPlanPtr SPI_prepare_cursor(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>,
1163 Oid * <parameter>argtypes</parameter>, int <parameter>cursorOptions</parameter>)
1164 </synopsis>
1165 </refsynopsisdiv>
1166
1167 <refsect1>
1168 <title>Description</title>
1169
1170 <para>
1171 <function>SPI_prepare_cursor</function> is identical to
1172 <function>SPI_prepare</function>, except that it also allows specification
1173 of the planner's <quote>cursor options</quote> parameter. This is a bit mask
1174 having the values shown in <filename>nodes/parsenodes.h</filename>
1175 for the <structfield>options</structfield> field of <structname>DeclareCursorStmt</structname>.
1176 <function>SPI_prepare</function> always takes the cursor options as zero.
1177 </para>
1178
1179 <para>
1180 This function is now deprecated in favor
1181 of <function>SPI_prepare_extended</function>.
1182 </para>
1183 </refsect1>
1184
1185 <refsect1>
1186 <title>Arguments</title>
1187
1188 <variablelist>
1189 <varlistentry>
1190 <term><literal>const char * <parameter>command</parameter></literal></term>
1191 <listitem>
1192 <para>
1193 command string
1194 </para>
1195 </listitem>
1196 </varlistentry>
1197
1198 <varlistentry>
1199 <term><literal>int <parameter>nargs</parameter></literal></term>
1200 <listitem>
1201 <para>
1202 number of input parameters (<literal>$1</literal>, <literal>$2</literal>, etc.)
1203 </para>
1204 </listitem>
1205 </varlistentry>
1206
1207 <varlistentry>
1208 <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
1209 <listitem>
1210 <para>
1211 pointer to an array containing the <acronym>OID</acronym>s of
1212 the data types of the parameters
1213 </para>
1214 </listitem>
1215 </varlistentry>
1216
1217 <varlistentry>
1218 <term><literal>int <parameter>cursorOptions</parameter></literal></term>
1219 <listitem>
1220 <para>
1221 integer bit mask of cursor options; zero produces default behavior
1222 </para>
1223 </listitem>
1224 </varlistentry>
1225 </variablelist>
1226 </refsect1>
1227
1228 <refsect1>
1229 <title>Return Value</title>
1230
1231 <para>
1232 <function>SPI_prepare_cursor</function> has the same return conventions as
1233 <function>SPI_prepare</function>.
1234 </para>
1235 </refsect1>
1236
1237 <refsect1>
1238 <title>Notes</title>
1239
1240 <para>
1241 Useful bits to set in <parameter>cursorOptions</parameter> include
1242 <symbol>CURSOR_OPT_SCROLL</symbol>,
1243 <symbol>CURSOR_OPT_NO_SCROLL</symbol>,
1244 <symbol>CURSOR_OPT_FAST_PLAN</symbol>,
1245 <symbol>CURSOR_OPT_GENERIC_PLAN</symbol>, and
1246 <symbol>CURSOR_OPT_CUSTOM_PLAN</symbol>. Note in particular that
1247 <symbol>CURSOR_OPT_HOLD</symbol> is ignored.
1248 </para>
1249 </refsect1>
1250 </refentry>
1251
1252 <!-- *********************************************** -->
1253
1254 <refentry id="spi-spi-prepare-extended">
1255 <indexterm><primary>SPI_prepare_extended</primary></indexterm>
1256
1257 <refmeta>
1258 <refentrytitle>SPI_prepare_extended</refentrytitle>
1259 <manvolnum>3</manvolnum>
1260 </refmeta>
1261
1262 <refnamediv>
1263 <refname>SPI_prepare_extended</refname>
1264 <refpurpose>prepare a statement, without executing it yet</refpurpose>
1265 </refnamediv>
1266
1267 <refsynopsisdiv>
1268 <synopsis>
1269 SPIPlanPtr SPI_prepare_extended(const char * <parameter>command</parameter>,
1270 const SPIPrepareOptions * <parameter>options</parameter>)
1271 </synopsis>
1272 </refsynopsisdiv>
1273
1274 <refsect1>
1275 <title>Description</title>
1276
1277 <para>
1278 <function>SPI_prepare_extended</function> creates and returns a prepared
1279 statement for the specified command, but doesn't execute the command.
1280 This function is equivalent to <function>SPI_prepare</function>,
1281 with the addition that the caller can specify options to control
1282 the parsing of external parameter references, as well as other facets
1283 of query parsing and planning.
1284 </para>
1285 </refsect1>
1286
1287 <refsect1>
1288 <title>Arguments</title>
1289
1290 <variablelist>
1291 <varlistentry>
1292 <term><literal>const char * <parameter>command</parameter></literal></term>
1293 <listitem>
1294 <para>
1295 command string
1296 </para>
1297 </listitem>
1298 </varlistentry>
1299
1300 <varlistentry>
1301 <term><literal>const SPIPrepareOptions * <parameter>options</parameter></literal></term>
1302 <listitem>
1303 <para>
1304 struct containing optional arguments
1305 </para>
1306 </listitem>
1307 </varlistentry>
1308 </variablelist>
1309
1310 <para>
1311 Callers should always zero out the entire <parameter>options</parameter>
1312 struct, then fill whichever fields they want to set. This ensures forward
1313 compatibility of code, since any fields that are added to the struct in
1314 future will be defined to behave backwards-compatibly if they are zero.
1315 The currently available <parameter>options</parameter> fields are:
1316 </para>
1317
1318 <variablelist>
1319 <varlistentry>
1320 <term><literal>ParserSetupHook <parameter>parserSetup</parameter></literal></term>
1321 <listitem>
1322 <para>
1323 Parser hook setup function
1324 </para>
1325 </listitem>
1326 </varlistentry>
1327
1328 <varlistentry>
1329 <term><literal>void * <parameter>parserSetupArg</parameter></literal></term>
1330 <listitem>
1331 <para>
1332 pass-through argument for <parameter>parserSetup</parameter>
1333 </para>
1334 </listitem>
1335 </varlistentry>
1336
1337 <varlistentry>
1338 <term><literal>RawParseMode <parameter>parseMode</parameter></literal></term>
1339 <listitem>
1340 <para>
1341 mode for raw parsing; <literal>RAW_PARSE_DEFAULT</literal> (zero)
1342 produces default behavior
1343 </para>
1344 </listitem>
1345 </varlistentry>
1346
1347 <varlistentry>
1348 <term><literal>int <parameter>cursorOptions</parameter></literal></term>
1349 <listitem>
1350 <para>
1351 integer bit mask of cursor options; zero produces default behavior
1352 </para>
1353 </listitem>
1354 </varlistentry>
1355 </variablelist>
1356 </refsect1>
1357
1358 <refsect1>
1359 <title>Return Value</title>
1360
1361 <para>
1362 <function>SPI_prepare_extended</function> has the same return conventions as
1363 <function>SPI_prepare</function>.
1364 </para>
1365 </refsect1>
1366 </refentry>
1367
1368 <!-- *********************************************** -->
1369
1370 <refentry id="spi-spi-prepare-params">
1371 <indexterm><primary>SPI_prepare_params</primary></indexterm>
1372
1373 <refmeta>
1374 <refentrytitle>SPI_prepare_params</refentrytitle>
1375 <manvolnum>3</manvolnum>
1376 </refmeta>
1377
1378 <refnamediv>
1379 <refname>SPI_prepare_params</refname>
1380 <refpurpose>prepare a statement, without executing it yet</refpurpose>
1381 </refnamediv>
1382
1383 <refsynopsisdiv>
1384 <synopsis>
1385 SPIPlanPtr SPI_prepare_params(const char * <parameter>command</parameter>,
1386 ParserSetupHook <parameter>parserSetup</parameter>,
1387 void * <parameter>parserSetupArg</parameter>,
1388 int <parameter>cursorOptions</parameter>)
1389 </synopsis>
1390 </refsynopsisdiv>
1391
1392 <refsect1>
1393 <title>Description</title>
1394
1395 <para>
1396 <function>SPI_prepare_params</function> creates and returns a prepared
1397 statement for the specified command, but doesn't execute the command.
1398 This function is equivalent to <function>SPI_prepare_cursor</function>,
1399 with the addition that the caller can specify parser hook functions
1400 to control the parsing of external parameter references.
1401 </para>
1402
1403 <para>
1404 This function is now deprecated in favor
1405 of <function>SPI_prepare_extended</function>.
1406 </para>
1407 </refsect1>
1408
1409 <refsect1>
1410 <title>Arguments</title>
1411
1412 <variablelist>
1413 <varlistentry>
1414 <term><literal>const char * <parameter>command</parameter></literal></term>
1415 <listitem>
1416 <para>
1417 command string
1418 </para>
1419 </listitem>
1420 </varlistentry>
1421
1422 <varlistentry>
1423 <term><literal>ParserSetupHook <parameter>parserSetup</parameter></literal></term>
1424 <listitem>
1425 <para>
1426 Parser hook setup function
1427 </para>
1428 </listitem>
1429 </varlistentry>
1430
1431 <varlistentry>
1432 <term><literal>void * <parameter>parserSetupArg</parameter></literal></term>
1433 <listitem>
1434 <para>
1435 pass-through argument for <parameter>parserSetup</parameter>
1436 </para>
1437 </listitem>
1438 </varlistentry>
1439
1440 <varlistentry>
1441 <term><literal>int <parameter>cursorOptions</parameter></literal></term>
1442 <listitem>
1443 <para>
1444 integer bit mask of cursor options; zero produces default behavior
1445 </para>
1446 </listitem>
1447 </varlistentry>
1448 </variablelist>
1449 </refsect1>
1450
1451 <refsect1>
1452 <title>Return Value</title>
1453
1454 <para>
1455 <function>SPI_prepare_params</function> has the same return conventions as
1456 <function>SPI_prepare</function>.
1457 </para>
1458 </refsect1>
1459 </refentry>
1460
1461 <!-- *********************************************** -->
1462
1463 <refentry id="spi-spi-getargcount">
1464 <indexterm><primary>SPI_getargcount</primary></indexterm>
1465
1466 <refmeta>
1467 <refentrytitle>SPI_getargcount</refentrytitle>
1468 <manvolnum>3</manvolnum>
1469 </refmeta>
1470
1471 <refnamediv>
1472 <refname>SPI_getargcount</refname>
1473 <refpurpose>return the number of arguments needed by a statement
1474 prepared by <function>SPI_prepare</function></refpurpose>
1475 </refnamediv>
1476
1477 <refsynopsisdiv>
1478 <synopsis>
1479 int SPI_getargcount(SPIPlanPtr <parameter>plan</parameter>)
1480 </synopsis>
1481 </refsynopsisdiv>
1482
1483 <refsect1>
1484 <title>Description</title>
1485
1486 <para>
1487 <function>SPI_getargcount</function> returns the number of arguments needed
1488 to execute a statement prepared by <function>SPI_prepare</function>.
1489 </para>
1490 </refsect1>
1491
1492 <refsect1>
1493 <title>Arguments</title>
1494
1495 <variablelist>
1496 <varlistentry>
1497 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1498 <listitem>
1499 <para>
1500 prepared statement (returned by <function>SPI_prepare</function>)
1501 </para>
1502 </listitem>
1503 </varlistentry>
1504 </variablelist>
1505 </refsect1>
1506
1507 <refsect1>
1508 <title>Return Value</title>
1509 <para>
1510 The count of expected arguments for the <parameter>plan</parameter>.
1511 If the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1512 <varname>SPI_result</varname> is set to <symbol>SPI_ERROR_ARGUMENT</symbol>
1513 and -1 is returned.
1514 </para>
1515 </refsect1>
1516 </refentry>
1517
1518 <!-- *********************************************** -->
1519
1520 <refentry id="spi-spi-getargtypeid">
1521 <indexterm><primary>SPI_getargtypeid</primary></indexterm>
1522
1523 <refmeta>
1524 <refentrytitle>SPI_getargtypeid</refentrytitle>
1525 <manvolnum>3</manvolnum>
1526 </refmeta>
1527
1528 <refnamediv>
1529 <refname>SPI_getargtypeid</refname>
1530 <refpurpose>return the data type OID for an argument of
1531 a statement prepared by <function>SPI_prepare</function></refpurpose>
1532 </refnamediv>
1533
1534 <refsynopsisdiv>
1535 <synopsis>
1536 Oid SPI_getargtypeid(SPIPlanPtr <parameter>plan</parameter>, int <parameter>argIndex</parameter>)
1537 </synopsis>
1538 </refsynopsisdiv>
1539
1540 <refsect1>
1541 <title>Description</title>
1542
1543 <para>
1544 <function>SPI_getargtypeid</function> returns the OID representing the type
1545 for the <parameter>argIndex</parameter>'th argument of a statement prepared by
1546 <function>SPI_prepare</function>. First argument is at index zero.
1547 </para>
1548 </refsect1>
1549
1550 <refsect1>
1551 <title>Arguments</title>
1552
1553 <variablelist>
1554 <varlistentry>
1555 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1556 <listitem>
1557 <para>
1558 prepared statement (returned by <function>SPI_prepare</function>)
1559 </para>
1560 </listitem>
1561 </varlistentry>
1562
1563 <varlistentry>
1564 <term><literal>int <parameter>argIndex</parameter></literal></term>
1565 <listitem>
1566 <para>
1567 zero based index of the argument
1568 </para>
1569 </listitem>
1570 </varlistentry>
1571 </variablelist>
1572 </refsect1>
1573
1574 <refsect1>
1575 <title>Return Value</title>
1576 <para>
1577 The type OID of the argument at the given index.
1578 If the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1579 or <parameter>argIndex</parameter> is less than 0 or
1580 not less than the number of arguments declared for the
1581 <parameter>plan</parameter>,
1582 <varname>SPI_result</varname> is set to <symbol>SPI_ERROR_ARGUMENT</symbol>
1583 and <symbol>InvalidOid</symbol> is returned.
1584 </para>
1585 </refsect1>
1586 </refentry>
1587
1588 <!-- *********************************************** -->
1589
1590 <refentry id="spi-spi-is-cursor-plan">
1591 <indexterm><primary>SPI_is_cursor_plan</primary></indexterm>
1592
1593 <refmeta>
1594 <refentrytitle>SPI_is_cursor_plan</refentrytitle>
1595 <manvolnum>3</manvolnum>
1596 </refmeta>
1597
1598 <refnamediv>
1599 <refname>SPI_is_cursor_plan</refname>
1600 <refpurpose>return <symbol>true</symbol> if a statement
1601 prepared by <function>SPI_prepare</function> can be used with
1602 <function>SPI_cursor_open</function></refpurpose>
1603 </refnamediv>
1604
1605 <refsynopsisdiv>
1606 <synopsis>
1607 bool SPI_is_cursor_plan(SPIPlanPtr <parameter>plan</parameter>)
1608 </synopsis>
1609 </refsynopsisdiv>
1610
1611 <refsect1>
1612 <title>Description</title>
1613
1614 <para>
1615 <function>SPI_is_cursor_plan</function> returns <symbol>true</symbol>
1616 if a statement prepared by <function>SPI_prepare</function> can be passed
1617 as an argument to <function>SPI_cursor_open</function>, or
1618 <symbol>false</symbol> if that is not the case. The criteria are that the
1619 <parameter>plan</parameter> represents one single command and that this
1620 command returns tuples to the caller; for example, <command>SELECT</command>
1621 is allowed unless it contains an <literal>INTO</literal> clause, and
1622 <command>UPDATE</command> is allowed only if it contains a <literal>RETURNING</literal>
1623 clause.
1624 </para>
1625 </refsect1>
1626
1627 <refsect1>
1628 <title>Arguments</title>
1629
1630 <variablelist>
1631 <varlistentry>
1632 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1633 <listitem>
1634 <para>
1635 prepared statement (returned by <function>SPI_prepare</function>)
1636 </para>
1637 </listitem>
1638 </varlistentry>
1639 </variablelist>
1640 </refsect1>
1641
1642 <refsect1>
1643 <title>Return Value</title>
1644 <para>
1645 <symbol>true</symbol> or <symbol>false</symbol> to indicate if the
1646 <parameter>plan</parameter> can produce a cursor or not, with
1647 <varname>SPI_result</varname> set to zero.
1648 If it is not possible to determine the answer (for example,
1649 if the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1650 or if called when not connected to SPI), then
1651 <varname>SPI_result</varname> is set to a suitable error code
1652 and <symbol>false</symbol> is returned.
1653 </para>
1654 </refsect1>
1655 </refentry>
1656
1657 <!-- *********************************************** -->
1658
1659 <refentry id="spi-spi-execute-plan">
1660 <indexterm><primary>SPI_execute_plan</primary></indexterm>
1661
1662 <refmeta>
1663 <refentrytitle>SPI_execute_plan</refentrytitle>
1664 <manvolnum>3</manvolnum>
1665 </refmeta>
1666
1667 <refnamediv>
1668 <refname>SPI_execute_plan</refname>
1669 <refpurpose>execute a statement prepared by <function>SPI_prepare</function></refpurpose>
1670 </refnamediv>
1671
1672 <refsynopsisdiv>
1673 <synopsis>
1674 int SPI_execute_plan(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
1675 bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
1676 </synopsis>
1677 </refsynopsisdiv>
1678
1679 <refsect1>
1680 <title>Description</title>
1681
1682 <para>
1683 <function>SPI_execute_plan</function> executes a statement prepared by
1684 <function>SPI_prepare</function> or one of its siblings.
1685 <parameter>read_only</parameter> and
1686 <parameter>count</parameter> have the same interpretation as in
1687 <function>SPI_execute</function>.
1688 </para>
1689 </refsect1>
1690
1691 <refsect1>
1692 <title>Arguments</title>
1693
1694 <variablelist>
1695 <varlistentry>
1696 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1697 <listitem>
1698 <para>
1699 prepared statement (returned by <function>SPI_prepare</function>)
1700 </para>
1701 </listitem>
1702 </varlistentry>
1703
1704 <varlistentry>
1705 <term><literal>Datum * <parameter>values</parameter></literal></term>
1706 <listitem>
1707 <para>
1708 An array of actual parameter values. Must have same length as the
1709 statement's number of arguments.
1710 </para>
1711 </listitem>
1712 </varlistentry>
1713
1714 <varlistentry>
1715 <term><literal>const char * <parameter>nulls</parameter></literal></term>
1716 <listitem>
1717 <para>
1718 An array describing which parameters are null. Must have same length as
1719 the statement's number of arguments.
1720 </para>
1721
1722 <para>
1723 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
1724 <function>SPI_execute_plan</function> assumes that no parameters
1725 are null. Otherwise, each entry of the <parameter>nulls</parameter>
1726 array should be <literal>'&nbsp;'</literal> if the corresponding parameter
1727 value is non-null, or <literal>'n'</literal> if the corresponding parameter
1728 value is null. (In the latter case, the actual value in the
1729 corresponding <parameter>values</parameter> entry doesn't matter.) Note
1730 that <parameter>nulls</parameter> is not a text string, just an array:
1731 it does not need a <literal>'\0'</literal> terminator.
1732 </para>
1733 </listitem>
1734 </varlistentry>
1735
1736 <varlistentry>
1737 <term><literal>bool <parameter>read_only</parameter></literal></term>
1738 <listitem>
1739 <para><literal>true</literal> for read-only execution</para>
1740 </listitem>
1741 </varlistentry>
1742
1743 <varlistentry>
1744 <term><literal>long <parameter>count</parameter></literal></term>
1745 <listitem>
1746 <para>
1747 maximum number of rows to return,
1748 or <literal>0</literal> for no limit
1749 </para>
1750 </listitem>
1751 </varlistentry>
1752 </variablelist>
1753 </refsect1>
1754
1755 <refsect1>
1756 <title>Return Value</title>
1757
1758 <para>
1759 The return value is the same as for <function>SPI_execute</function>,
1760 with the following additional possible error (negative) results:
1761
1762 <variablelist>
1763 <varlistentry>
1764 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
1765 <listitem>
1766 <para>
1767 if <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1768 or <parameter>count</parameter> is less than 0
1769 </para>
1770 </listitem>
1771 </varlistentry>
1772
1773 <varlistentry>
1774 <term><symbol>SPI_ERROR_PARAM</symbol></term>
1775 <listitem>
1776 <para>
1777 if <parameter>values</parameter> is <symbol>NULL</symbol> and
1778 <parameter>plan</parameter> was prepared with some parameters
1779 </para>
1780 </listitem>
1781 </varlistentry>
1782 </variablelist>
1783 </para>
1784
1785 <para>
1786 <varname>SPI_processed</varname> and
1787 <varname>SPI_tuptable</varname> are set as in
1788 <function>SPI_execute</function> if successful.
1789 </para>
1790 </refsect1>
1791 </refentry>
1792
1793 <!-- *********************************************** -->
1794
1795 <refentry id="spi-spi-execute-plan-extended">
1796 <indexterm><primary>SPI_execute_plan_extended</primary></indexterm>
1797
1798 <refmeta>
1799 <refentrytitle>SPI_execute_plan_extended</refentrytitle>
1800 <manvolnum>3</manvolnum>
1801 </refmeta>
1802
1803 <refnamediv>
1804 <refname>SPI_execute_plan_extended</refname>
1805 <refpurpose>execute a statement prepared by <function>SPI_prepare</function></refpurpose>
1806 </refnamediv>
1807
1808 <refsynopsisdiv>
1809 <synopsis>
1810 int SPI_execute_plan_extended(SPIPlanPtr <parameter>plan</parameter>,
1811 const SPIExecuteOptions * <parameter>options</parameter>)
1812 </synopsis>
1813 </refsynopsisdiv>
1814
1815 <refsect1>
1816 <title>Description</title>
1817
1818 <para>
1819 <function>SPI_execute_plan_extended</function> executes a statement
1820 prepared by <function>SPI_prepare</function> or one of its siblings.
1821 This function is equivalent to <function>SPI_execute_plan</function>,
1822 except that information about the parameter values to be passed to the
1823 query is presented differently, and additional execution-controlling
1824 options can be passed.
1825 </para>
1826
1827 <para>
1828 Query parameter values are represented by
1829 a <literal>ParamListInfo</literal> struct, which is convenient for passing
1830 down values that are already available in that format. Dynamic parameter
1831 sets can also be used, via hook functions specified
1832 in <literal>ParamListInfo</literal>.
1833 </para>
1834
1835 <para>
1836 Also, instead of always accumulating the result tuples into a
1837 <varname>SPI_tuptable</varname> structure, tuples can be passed to a
1838 caller-supplied <literal>DestReceiver</literal> object as they are
1839 generated by the executor. This is particularly helpful for queries
1840 that might generate many tuples, since the data can be processed
1841 on-the-fly instead of being accumulated in memory.
1842 </para>
1843 </refsect1>
1844
1845 <refsect1>
1846 <title>Arguments</title>
1847
1848 <variablelist>
1849 <varlistentry>
1850 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1851 <listitem>
1852 <para>
1853 prepared statement (returned by <function>SPI_prepare</function>)
1854 </para>
1855 </listitem>
1856 </varlistentry>
1857
1858 <varlistentry>
1859 <term><literal>const SPIExecuteOptions * <parameter>options</parameter></literal></term>
1860 <listitem>
1861 <para>
1862 struct containing optional arguments
1863 </para>
1864 </listitem>
1865 </varlistentry>
1866 </variablelist>
1867
1868 <para>
1869 Callers should always zero out the entire <parameter>options</parameter>
1870 struct, then fill whichever fields they want to set. This ensures forward
1871 compatibility of code, since any fields that are added to the struct in
1872 future will be defined to behave backwards-compatibly if they are zero.
1873 The currently available <parameter>options</parameter> fields are:
1874 </para>
1875
1876 <variablelist>
1877 <varlistentry>
1878 <term><literal>ParamListInfo <parameter>params</parameter></literal></term>
1879 <listitem>
1880 <para>
1881 data structure containing query parameter types and values; NULL if none
1882 </para>
1883 </listitem>
1884 </varlistentry>
1885
1886 <varlistentry>
1887 <term><literal>bool <parameter>read_only</parameter></literal></term>
1888 <listitem>
1889 <para><literal>true</literal> for read-only execution</para>
1890 </listitem>
1891 </varlistentry>
1892
1893 <varlistentry>
1894 <term><literal>bool <parameter>allow_nonatomic</parameter></literal></term>
1895 <listitem>
1896 <para>
1897 <literal>true</literal> allows non-atomic execution of CALL and DO
1898 statements (but this field is ignored unless
1899 the <symbol>SPI_OPT_NONATOMIC</symbol> flag was passed
1900 to <function>SPI_connect_ext</function>)
1901 </para>
1902 </listitem>
1903 </varlistentry>
1904
1905 <varlistentry>
1906 <term><literal>bool <parameter>must_return_tuples</parameter></literal></term>
1907 <listitem>
1908 <para>
1909 if <literal>true</literal>, raise error if the query is not of a kind
1910 that returns tuples (this does not forbid the case where it happens to
1911 return zero tuples)
1912 </para>
1913 </listitem>
1914 </varlistentry>
1915
1916 <varlistentry>
1917 <term><literal>uint64 <parameter>tcount</parameter></literal></term>
1918 <listitem>
1919 <para>
1920 maximum number of rows to return,
1921 or <literal>0</literal> for no limit
1922 </para>
1923 </listitem>
1924 </varlistentry>
1925
1926 <varlistentry>
1927 <term><literal>DestReceiver * <parameter>dest</parameter></literal></term>
1928 <listitem>
1929 <para>
1930 <literal>DestReceiver</literal> object that will receive any tuples
1931 emitted by the query; if NULL, result tuples are accumulated into
1932 a <varname>SPI_tuptable</varname> structure, as
1933 in <function>SPI_execute_plan</function>
1934 </para>
1935 </listitem>
1936 </varlistentry>
1937
1938 <varlistentry>
1939 <term><literal>ResourceOwner <parameter>owner</parameter></literal></term>
1940 <listitem>
1941 <para>
1942 The resource owner that will hold a reference count on the plan while
1943 it is executed. If NULL, CurrentResourceOwner is used. Ignored for
1944 non-saved plans, as SPI does not acquire reference counts on those.
1945 </para>
1946 </listitem>
1947 </varlistentry>
1948 </variablelist>
1949 </refsect1>
1950
1951 <refsect1>
1952 <title>Return Value</title>
1953
1954 <para>
1955 The return value is the same as for <function>SPI_execute_plan</function>.
1956 </para>
1957
1958 <para>
1959 When <parameter>options-&gt;dest</parameter> is NULL,
1960 <varname>SPI_processed</varname> and
1961 <varname>SPI_tuptable</varname> are set as in
1962 <function>SPI_execute_plan</function>.
1963 When <parameter>options-&gt;dest</parameter> is not NULL,
1964 <varname>SPI_processed</varname> is set to zero and
1965 <varname>SPI_tuptable</varname> is set to NULL. If a tuple count
1966 is required, the caller's <literal>DestReceiver</literal> object must
1967 calculate it.
1968 </para>
1969 </refsect1>
1970 </refentry>
1971
1972 <!-- *********************************************** -->
1973
1974 <refentry id="spi-spi-execute-plan-with-paramlist">
1975 <indexterm><primary>SPI_execute_plan_with_paramlist</primary></indexterm>
1976
1977 <refmeta>
1978 <refentrytitle>SPI_execute_plan_with_paramlist</refentrytitle>
1979 <manvolnum>3</manvolnum>
1980 </refmeta>
1981
1982 <refnamediv>
1983 <refname>SPI_execute_plan_with_paramlist</refname>
1984 <refpurpose>execute a statement prepared by <function>SPI_prepare</function></refpurpose>
1985 </refnamediv>
1986
1987 <refsynopsisdiv>
1988 <synopsis>
1989 int SPI_execute_plan_with_paramlist(SPIPlanPtr <parameter>plan</parameter>,
1990 ParamListInfo <parameter>params</parameter>,
1991 bool <parameter>read_only</parameter>,
1992 long <parameter>count</parameter>)
1993 </synopsis>
1994 </refsynopsisdiv>
1995
1996 <refsect1>
1997 <title>Description</title>
1998
1999 <para>
2000 <function>SPI_execute_plan_with_paramlist</function> executes a statement
2001 prepared by <function>SPI_prepare</function>.
2002 This function is equivalent to <function>SPI_execute_plan</function>
2003 except that information about the parameter values to be passed to the
2004 query is presented differently. The <literal>ParamListInfo</literal>
2005 representation can be convenient for passing down values that are
2006 already available in that format. It also supports use of dynamic
2007 parameter sets via hook functions specified in <literal>ParamListInfo</literal>.
2008 </para>
2009
2010 <para>
2011 This function is now deprecated in favor
2012 of <function>SPI_execute_plan_extended</function>.
2013 </para>
2014 </refsect1>
2015
2016 <refsect1>
2017 <title>Arguments</title>
2018
2019 <variablelist>
2020 <varlistentry>
2021 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
2022 <listitem>
2023 <para>
2024 prepared statement (returned by <function>SPI_prepare</function>)
2025 </para>
2026 </listitem>
2027 </varlistentry>
2028
2029 <varlistentry>
2030 <term><literal>ParamListInfo <parameter>params</parameter></literal></term>
2031 <listitem>
2032 <para>
2033 data structure containing parameter types and values; NULL if none
2034 </para>
2035 </listitem>
2036 </varlistentry>
2037
2038 <varlistentry>
2039 <term><literal>bool <parameter>read_only</parameter></literal></term>
2040 <listitem>
2041 <para><literal>true</literal> for read-only execution</para>
2042 </listitem>
2043 </varlistentry>
2044
2045 <varlistentry>
2046 <term><literal>long <parameter>count</parameter></literal></term>
2047 <listitem>
2048 <para>
2049 maximum number of rows to return,
2050 or <literal>0</literal> for no limit
2051 </para>
2052 </listitem>
2053 </varlistentry>
2054 </variablelist>
2055 </refsect1>
2056
2057 <refsect1>
2058 <title>Return Value</title>
2059
2060 <para>
2061 The return value is the same as for <function>SPI_execute_plan</function>.
2062 </para>
2063
2064 <para>
2065 <varname>SPI_processed</varname> and
2066 <varname>SPI_tuptable</varname> are set as in
2067 <function>SPI_execute_plan</function> if successful.
2068 </para>
2069 </refsect1>
2070 </refentry>
2071
2072 <!-- *********************************************** -->
2073
2074 <refentry id="spi-spi-execp">
2075 <indexterm><primary>SPI_execp</primary></indexterm>
2076
2077 <refmeta>
2078 <refentrytitle>SPI_execp</refentrytitle>
2079 <manvolnum>3</manvolnum>
2080 </refmeta>
2081
2082 <refnamediv>
2083 <refname>SPI_execp</refname>
2084 <refpurpose>execute a statement in read/write mode</refpurpose>
2085 </refnamediv>
2086
2087 <refsynopsisdiv>
2088 <synopsis>
2089 int SPI_execp(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, long <parameter>count</parameter>)
2090 </synopsis>
2091 </refsynopsisdiv>
2092
2093 <refsect1>
2094 <title>Description</title>
2095
2096 <para>
2097 <function>SPI_execp</function> is the same as
2098 <function>SPI_execute_plan</function>, with the latter's
2099 <parameter>read_only</parameter> parameter always taken as
2100 <literal>false</literal>.
2101 </para>
2102 </refsect1>
2103
2104 <refsect1>
2105 <title>Arguments</title>
2106
2107 <variablelist>
2108 <varlistentry>
2109 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
2110 <listitem>
2111 <para>
2112 prepared statement (returned by <function>SPI_prepare</function>)
2113 </para>
2114 </listitem>
2115 </varlistentry>
2116
2117 <varlistentry>
2118 <term><literal>Datum * <parameter>values</parameter></literal></term>
2119 <listitem>
2120 <para>
2121 An array of actual parameter values. Must have same length as the
2122 statement's number of arguments.
2123 </para>
2124 </listitem>
2125 </varlistentry>
2126
2127 <varlistentry>
2128 <term><literal>const char * <parameter>nulls</parameter></literal></term>
2129 <listitem>
2130 <para>
2131 An array describing which parameters are null. Must have same length as
2132 the statement's number of arguments.
2133 </para>
2134
2135 <para>
2136 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
2137 <function>SPI_execp</function> assumes that no parameters
2138 are null. Otherwise, each entry of the <parameter>nulls</parameter>
2139 array should be <literal>'&nbsp;'</literal> if the corresponding parameter
2140 value is non-null, or <literal>'n'</literal> if the corresponding parameter
2141 value is null. (In the latter case, the actual value in the
2142 corresponding <parameter>values</parameter> entry doesn't matter.) Note
2143 that <parameter>nulls</parameter> is not a text string, just an array:
2144 it does not need a <literal>'\0'</literal> terminator.
2145 </para>
2146 </listitem>
2147 </varlistentry>
2148
2149 <varlistentry>
2150 <term><literal>long <parameter>count</parameter></literal></term>
2151 <listitem>
2152 <para>
2153 maximum number of rows to return,
2154 or <literal>0</literal> for no limit
2155 </para>
2156 </listitem>
2157 </varlistentry>
2158 </variablelist>
2159 </refsect1>
2160
2161 <refsect1>
2162 <title>Return Value</title>
2163
2164 <para>
2165 See <function>SPI_execute_plan</function>.
2166 </para>
2167
2168 <para>
2169 <varname>SPI_processed</varname> and
2170 <varname>SPI_tuptable</varname> are set as in
2171 <function>SPI_execute</function> if successful.
2172 </para>
2173 </refsect1>
2174 </refentry>
2175
2176 <!-- *********************************************** -->
2177
2178 <refentry id="spi-spi-cursor-open">
2179 <indexterm><primary>SPI_cursor_open</primary></indexterm>
2180
2181 <refmeta>
2182 <refentrytitle>SPI_cursor_open</refentrytitle>
2183 <manvolnum>3</manvolnum>
2184 </refmeta>
2185
2186 <refnamediv>
2187 <refname>SPI_cursor_open</refname>
2188 <refpurpose>set up a cursor using a statement created with <function>SPI_prepare</function></refpurpose>
2189 </refnamediv>
2190
2191 <refsynopsisdiv>
2192 <synopsis>
2193 Portal SPI_cursor_open(const char * <parameter>name</parameter>, SPIPlanPtr <parameter>plan</parameter>,
2194 Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
2195 bool <parameter>read_only</parameter>)
2196 </synopsis>
2197 </refsynopsisdiv>
2198
2199 <refsect1>
2200 <title>Description</title>
2201
2202 <para>
2203 <function>SPI_cursor_open</function> sets up a cursor (internally,
2204 a portal) that will execute a statement prepared by
2205 <function>SPI_prepare</function>. The parameters have the same
2206 meanings as the corresponding parameters to
2207 <function>SPI_execute_plan</function>.
2208 </para>
2209
2210 <para>
2211 Using a cursor instead of executing the statement directly has two
2212 benefits. First, the result rows can be retrieved a few at a time,
2213 avoiding memory overrun for queries that return many rows. Second,
2214 a portal can outlive the current C function (it can, in fact, live
2215 to the end of the current transaction). Returning the portal name
2216 to the C function's caller provides a way of returning a row set as
2217 result.
2218 </para>
2219
2220 <para>
2221 The passed-in parameter data will be copied into the cursor's portal, so it
2222 can be freed while the cursor still exists.
2223 </para>
2224 </refsect1>
2225
2226 <refsect1>
2227 <title>Arguments</title>
2228
2229 <variablelist>
2230 <varlistentry>
2231 <term><literal>const char * <parameter>name</parameter></literal></term>
2232 <listitem>
2233 <para>
2234 name for portal, or <symbol>NULL</symbol> to let the system
2235 select a name
2236 </para>
2237 </listitem>
2238 </varlistentry>
2239
2240 <varlistentry>
2241 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
2242 <listitem>
2243 <para>
2244 prepared statement (returned by <function>SPI_prepare</function>)
2245 </para>
2246 </listitem>
2247 </varlistentry>
2248
2249 <varlistentry>
2250 <term><literal>Datum * <parameter>values</parameter></literal></term>
2251 <listitem>
2252 <para>
2253 An array of actual parameter values. Must have same length as the
2254 statement's number of arguments.
2255 </para>
2256 </listitem>
2257 </varlistentry>
2258
2259 <varlistentry>
2260 <term><literal>const char * <parameter>nulls</parameter></literal></term>
2261 <listitem>
2262 <para>
2263 An array describing which parameters are null. Must have same length as
2264 the statement's number of arguments.
2265 </para>
2266
2267 <para>
2268 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
2269 <function>SPI_cursor_open</function> assumes that no parameters
2270 are null. Otherwise, each entry of the <parameter>nulls</parameter>
2271 array should be <literal>'&nbsp;'</literal> if the corresponding parameter
2272 value is non-null, or <literal>'n'</literal> if the corresponding parameter
2273 value is null. (In the latter case, the actual value in the
2274 corresponding <parameter>values</parameter> entry doesn't matter.) Note
2275 that <parameter>nulls</parameter> is not a text string, just an array:
2276 it does not need a <literal>'\0'</literal> terminator.
2277 </para>
2278 </listitem>
2279 </varlistentry>
2280
2281 <varlistentry>
2282 <term><literal>bool <parameter>read_only</parameter></literal></term>
2283 <listitem>
2284 <para><literal>true</literal> for read-only execution</para>
2285 </listitem>
2286 </varlistentry>
2287 </variablelist>
2288 </refsect1>
2289
2290 <refsect1>
2291 <title>Return Value</title>
2292
2293 <para>
2294 Pointer to portal containing the cursor. Note there is no error
2295 return convention; any error will be reported via <function>elog</function>.
2296 </para>
2297 </refsect1>
2298 </refentry>
2299
2300 <!-- *********************************************** -->
2301
2302 <refentry id="spi-spi-cursor-open-with-args">
2303 <indexterm><primary>SPI_cursor_open_with_args</primary></indexterm>
2304
2305 <refmeta>
2306 <refentrytitle>SPI_cursor_open_with_args</refentrytitle>
2307 <manvolnum>3</manvolnum>
2308 </refmeta>
2309
2310 <refnamediv>
2311 <refname>SPI_cursor_open_with_args</refname>
2312 <refpurpose>set up a cursor using a query and parameters</refpurpose>
2313 </refnamediv>
2314
2315 <refsynopsisdiv>
2316 <synopsis>
2317 Portal SPI_cursor_open_with_args(const char *<parameter>name</parameter>,
2318 const char *<parameter>command</parameter>,
2319 int <parameter>nargs</parameter>, Oid *<parameter>argtypes</parameter>,
2320 Datum *<parameter>values</parameter>, const char *<parameter>nulls</parameter>,
2321 bool <parameter>read_only</parameter>, int <parameter>cursorOptions</parameter>)
2322 </synopsis>
2323 </refsynopsisdiv>
2324
2325 <refsect1>
2326 <title>Description</title>
2327
2328 <para>
2329 <function>SPI_cursor_open_with_args</function> sets up a cursor
2330 (internally, a portal) that will execute the specified query.
2331 Most of the parameters have the same meanings as the corresponding
2332 parameters to <function>SPI_prepare_cursor</function>
2333 and <function>SPI_cursor_open</function>.
2334 </para>
2335
2336 <para>
2337 For one-time query execution, this function should be preferred
2338 over <function>SPI_prepare_cursor</function> followed by
2339 <function>SPI_cursor_open</function>.
2340 If the same command is to be executed with many different parameters,
2341 either method might be faster, depending on the cost of re-planning
2342 versus the benefit of custom plans.
2343 </para>
2344
2345 <para>
2346 The passed-in parameter data will be copied into the cursor's portal, so it
2347 can be freed while the cursor still exists.
2348 </para>
2349
2350 <para>
2351 This function is now deprecated in favor
2352 of <function>SPI_cursor_parse_open</function>, which provides equivalent
2353 functionality using a more modern API for handling query parameters.
2354 </para>
2355 </refsect1>
2356
2357 <refsect1>
2358 <title>Arguments</title>
2359
2360 <variablelist>
2361 <varlistentry>
2362 <term><literal>const char * <parameter>name</parameter></literal></term>
2363 <listitem>
2364 <para>
2365 name for portal, or <symbol>NULL</symbol> to let the system
2366 select a name
2367 </para>
2368 </listitem>
2369 </varlistentry>
2370
2371 <varlistentry>
2372 <term><literal>const char * <parameter>command</parameter></literal></term>
2373 <listitem>
2374 <para>
2375 command string
2376 </para>
2377 </listitem>
2378 </varlistentry>
2379
2380 <varlistentry>
2381 <term><literal>int <parameter>nargs</parameter></literal></term>
2382 <listitem>
2383 <para>
2384 number of input parameters (<literal>$1</literal>, <literal>$2</literal>, etc.)
2385 </para>
2386 </listitem>
2387 </varlistentry>
2388
2389 <varlistentry>
2390 <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
2391 <listitem>
2392 <para>
2393 an array of length <parameter>nargs</parameter>, containing the
2394 <acronym>OID</acronym>s of the data types of the parameters
2395 </para>
2396 </listitem>
2397 </varlistentry>
2398
2399 <varlistentry>
2400 <term><literal>Datum * <parameter>values</parameter></literal></term>
2401 <listitem>
2402 <para>
2403 an array of length <parameter>nargs</parameter>, containing the actual
2404 parameter values
2405 </para>
2406 </listitem>
2407 </varlistentry>
2408
2409 <varlistentry>
2410 <term><literal>const char * <parameter>nulls</parameter></literal></term>
2411 <listitem>
2412 <para>
2413 an array of length <parameter>nargs</parameter>, describing which
2414 parameters are null
2415 </para>
2416
2417 <para>
2418 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
2419 <function>SPI_cursor_open_with_args</function> assumes that no parameters
2420 are null. Otherwise, each entry of the <parameter>nulls</parameter>
2421 array should be <literal>'&nbsp;'</literal> if the corresponding parameter
2422 value is non-null, or <literal>'n'</literal> if the corresponding parameter
2423 value is null. (In the latter case, the actual value in the
2424 corresponding <parameter>values</parameter> entry doesn't matter.) Note
2425 that <parameter>nulls</parameter> is not a text string, just an array:
2426 it does not need a <literal>'\0'</literal> terminator.
2427 </para>
2428 </listitem>
2429 </varlistentry>
2430
2431 <varlistentry>
2432 <term><literal>bool <parameter>read_only</parameter></literal></term>
2433 <listitem>
2434 <para><literal>true</literal> for read-only execution</para>
2435 </listitem>
2436 </varlistentry>
2437
2438 <varlistentry>
2439 <term><literal>int <parameter>cursorOptions</parameter></literal></term>
2440 <listitem>
2441 <para>
2442 integer bit mask of cursor options; zero produces default behavior
2443 </para>
2444 </listitem>
2445 </varlistentry>
2446 </variablelist>
2447 </refsect1>
2448
2449 <refsect1>
2450 <title>Return Value</title>
2451
2452 <para>
2453 Pointer to portal containing the cursor. Note there is no error
2454 return convention; any error will be reported via <function>elog</function>.
2455 </para>
2456 </refsect1>
2457 </refentry>
2458
2459 <!-- *********************************************** -->
2460
2461 <refentry id="spi-spi-cursor-open-with-paramlist">
2462 <indexterm><primary>SPI_cursor_open_with_paramlist</primary></indexterm>
2463
2464 <refmeta>
2465 <refentrytitle>SPI_cursor_open_with_paramlist</refentrytitle>
2466 <manvolnum>3</manvolnum>
2467 </refmeta>
2468
2469 <refnamediv>
2470 <refname>SPI_cursor_open_with_paramlist</refname>
2471 <refpurpose>set up a cursor using parameters</refpurpose>
2472 </refnamediv>
2473
2474 <refsynopsisdiv>
2475 <synopsis>
2476 Portal SPI_cursor_open_with_paramlist(const char *<parameter>name</parameter>,
2477 SPIPlanPtr <parameter>plan</parameter>,
2478 ParamListInfo <parameter>params</parameter>,
2479 bool <parameter>read_only</parameter>)
2480 </synopsis>
2481 </refsynopsisdiv>
2482
2483 <refsect1>
2484 <title>Description</title>
2485
2486 <para>
2487 <function>SPI_cursor_open_with_paramlist</function> sets up a cursor
2488 (internally, a portal) that will execute a statement prepared by
2489 <function>SPI_prepare</function>.
2490 This function is equivalent to <function>SPI_cursor_open</function>
2491 except that information about the parameter values to be passed to the
2492 query is presented differently. The <literal>ParamListInfo</literal>
2493 representation can be convenient for passing down values that are
2494 already available in that format. It also supports use of dynamic
2495 parameter sets via hook functions specified in <literal>ParamListInfo</literal>.
2496 </para>
2497
2498 <para>
2499 The passed-in parameter data will be copied into the cursor's portal, so it
2500 can be freed while the cursor still exists.
2501 </para>
2502 </refsect1>
2503
2504 <refsect1>
2505 <title>Arguments</title>
2506
2507 <variablelist>
2508 <varlistentry>
2509 <term><literal>const char * <parameter>name</parameter></literal></term>
2510 <listitem>
2511 <para>
2512 name for portal, or <symbol>NULL</symbol> to let the system
2513 select a name
2514 </para>
2515 </listitem>
2516 </varlistentry>
2517
2518 <varlistentry>
2519 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
2520 <listitem>
2521 <para>
2522 prepared statement (returned by <function>SPI_prepare</function>)
2523 </para>
2524 </listitem>
2525 </varlistentry>
2526
2527 <varlistentry>
2528 <term><literal>ParamListInfo <parameter>params</parameter></literal></term>
2529 <listitem>
2530 <para>
2531 data structure containing parameter types and values; NULL if none
2532 </para>
2533 </listitem>
2534 </varlistentry>
2535
2536 <varlistentry>
2537 <term><literal>bool <parameter>read_only</parameter></literal></term>
2538 <listitem>
2539 <para><literal>true</literal> for read-only execution</para>
2540 </listitem>
2541 </varlistentry>
2542 </variablelist>
2543 </refsect1>
2544
2545 <refsect1>
2546 <title>Return Value</title>
2547
2548 <para>
2549 Pointer to portal containing the cursor. Note there is no error
2550 return convention; any error will be reported via <function>elog</function>.
2551 </para>
2552 </refsect1>
2553 </refentry>
2554
2555 <!-- *********************************************** -->
2556
2557 <refentry id="spi-spi-cursor-parse-open">
2558 <indexterm><primary>SPI_cursor_parse_open</primary></indexterm>
2559
2560 <refmeta>
2561 <refentrytitle>SPI_cursor_parse_open</refentrytitle>
2562 <manvolnum>3</manvolnum>
2563 </refmeta>
2564
2565 <refnamediv>
2566 <refname>SPI_cursor_parse_open</refname>
2567 <refpurpose>set up a cursor using a query string and parameters</refpurpose>
2568 </refnamediv>
2569
2570 <refsynopsisdiv>
2571 <synopsis>
2572 Portal SPI_cursor_parse_open(const char *<parameter>name</parameter>,
2573 const char *<parameter>command</parameter>,
2574 const SPIParseOpenOptions * <parameter>options</parameter>)
2575 </synopsis>
2576 </refsynopsisdiv>
2577
2578 <refsect1>
2579 <title>Description</title>
2580
2581 <para>
2582 <function>SPI_cursor_parse_open</function> sets up a cursor
2583 (internally, a portal) that will execute the specified query string.
2584 This is comparable to <function>SPI_prepare_cursor</function> followed
2585 by <function>SPI_cursor_open_with_paramlist</function>, except that
2586 parameter references within the query string are handled entirely by
2587 supplying a <literal>ParamListInfo</literal> object.
2588 </para>
2589
2590 <para>
2591 For one-time query execution, this function should be preferred
2592 over <function>SPI_prepare_cursor</function> followed by
2593 <function>SPI_cursor_open_with_paramlist</function>.
2594 If the same command is to be executed with many different parameters,
2595 either method might be faster, depending on the cost of re-planning
2596 versus the benefit of custom plans.
2597 </para>
2598
2599 <para>
2600 The <parameter>options-&gt;params</parameter> object should normally
2601 mark each parameter with the <literal>PARAM_FLAG_CONST</literal> flag,
2602 since a one-shot plan is always used for the query.
2603 </para>
2604
2605 <para>
2606 The passed-in parameter data will be copied into the cursor's portal, so it
2607 can be freed while the cursor still exists.
2608 </para>
2609 </refsect1>
2610
2611 <refsect1>
2612 <title>Arguments</title>
2613
2614 <variablelist>
2615 <varlistentry>
2616 <term><literal>const char * <parameter>name</parameter></literal></term>
2617 <listitem>
2618 <para>
2619 name for portal, or <symbol>NULL</symbol> to let the system
2620 select a name
2621 </para>
2622 </listitem>
2623 </varlistentry>
2624
2625 <varlistentry>
2626 <term><literal>const char * <parameter>command</parameter></literal></term>
2627 <listitem>
2628 <para>
2629 command string
2630 </para>
2631 </listitem>
2632 </varlistentry>
2633
2634 <varlistentry>
2635 <term><literal>const SPIParseOpenOptions * <parameter>options</parameter></literal></term>
2636 <listitem>
2637 <para>
2638 struct containing optional arguments
2639 </para>
2640 </listitem>
2641 </varlistentry>
2642 </variablelist>
2643
2644 <para>
2645 Callers should always zero out the entire <parameter>options</parameter>
2646 struct, then fill whichever fields they want to set. This ensures forward
2647 compatibility of code, since any fields that are added to the struct in
2648 future will be defined to behave backwards-compatibly if they are zero.
2649 The currently available <parameter>options</parameter> fields are:
2650 </para>
2651
2652 <variablelist>
2653 <varlistentry>
2654 <term><literal>ParamListInfo <parameter>params</parameter></literal></term>
2655 <listitem>
2656 <para>
2657 data structure containing query parameter types and values; NULL if none
2658 </para>
2659 </listitem>
2660 </varlistentry>
2661
2662 <varlistentry>
2663 <term><literal>int <parameter>cursorOptions</parameter></literal></term>
2664 <listitem>
2665 <para>
2666 integer bit mask of cursor options; zero produces default behavior
2667 </para>
2668 </listitem>
2669 </varlistentry>
2670
2671 <varlistentry>
2672 <term><literal>bool <parameter>read_only</parameter></literal></term>
2673 <listitem>
2674 <para><literal>true</literal> for read-only execution</para>
2675 </listitem>
2676 </varlistentry>
2677 </variablelist>
2678 </refsect1>
2679
2680 <refsect1>
2681 <title>Return Value</title>
2682
2683 <para>
2684 Pointer to portal containing the cursor. Note there is no error
2685 return convention; any error will be reported via <function>elog</function>.
2686 </para>
2687 </refsect1>
2688 </refentry>
2689
2690 <!-- *********************************************** -->
2691
2692 <refentry id="spi-spi-cursor-find">
2693 <indexterm><primary>SPI_cursor_find</primary></indexterm>
2694
2695 <refmeta>
2696 <refentrytitle>SPI_cursor_find</refentrytitle>
2697 <manvolnum>3</manvolnum>
2698 </refmeta>
2699
2700 <refnamediv>
2701 <refname>SPI_cursor_find</refname>
2702 <refpurpose>find an existing cursor by name</refpurpose>
2703 </refnamediv>
2704
2705 <refsynopsisdiv>
2706 <synopsis>
2707 Portal SPI_cursor_find(const char * <parameter>name</parameter>)
2708 </synopsis>
2709 </refsynopsisdiv>
2710
2711 <refsect1>
2712 <title>Description</title>
2713
2714 <para>
2715 <function>SPI_cursor_find</function> finds an existing portal by
2716 name. This is primarily useful to resolve a cursor name returned
2717 as text by some other function.
2718 </para>
2719 </refsect1>
2720
2721 <refsect1>
2722 <title>Arguments</title>
2723
2724 <variablelist>
2725 <varlistentry>
2726 <term><literal>const char * <parameter>name</parameter></literal></term>
2727 <listitem>
2728 <para>
2729 name of the portal
2730 </para>
2731 </listitem>
2732 </varlistentry>
2733 </variablelist>
2734 </refsect1>
2735
2736 <refsect1>
2737 <title>Return Value</title>
2738
2739 <para>
2740 pointer to the portal with the specified name, or
2741 <symbol>NULL</symbol> if none was found
2742 </para>
2743 </refsect1>
2744
2745 <refsect1>
2746 <title>Notes</title>
2747
2748 <para>
2749 Beware that this function can return a <type>Portal</type> object
2750 that does not have cursor-like properties; for example it might not
2751 return tuples. If you simply pass the <type>Portal</type> pointer
2752 to other SPI functions, they can defend themselves against such
2753 cases, but caution is appropriate when directly inspecting
2754 the <type>Portal</type>.
2755 </para>
2756 </refsect1>
2757 </refentry>
2758
2759 <!-- *********************************************** -->
2760
2761 <refentry id="spi-spi-cursor-fetch">
2762 <indexterm><primary>SPI_cursor_fetch</primary></indexterm>
2763
2764 <refmeta>
2765 <refentrytitle>SPI_cursor_fetch</refentrytitle>
2766 <manvolnum>3</manvolnum>
2767 </refmeta>
2768
2769 <refnamediv>
2770 <refname>SPI_cursor_fetch</refname>
2771 <refpurpose>fetch some rows from a cursor</refpurpose>
2772 </refnamediv>
2773
2774 <refsynopsisdiv>
2775 <synopsis>
2776 void SPI_cursor_fetch(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, long <parameter>count</parameter>)
2777 </synopsis>
2778 </refsynopsisdiv>
2779
2780 <refsect1>
2781 <title>Description</title>
2782
2783 <para>
2784 <function>SPI_cursor_fetch</function> fetches some rows from a
2785 cursor. This is equivalent to a subset of the SQL command
2786 <command>FETCH</command> (see <function>SPI_scroll_cursor_fetch</function>
2787 for more functionality).
2788 </para>
2789 </refsect1>
2790
2791 <refsect1>
2792 <title>Arguments</title>
2793
2794 <variablelist>
2795 <varlistentry>
2796 <term><literal>Portal <parameter>portal</parameter></literal></term>
2797 <listitem>
2798 <para>
2799 portal containing the cursor
2800 </para>
2801 </listitem>
2802 </varlistentry>
2803
2804 <varlistentry>
2805 <term><literal>bool <parameter>forward</parameter></literal></term>
2806 <listitem>
2807 <para>
2808 true for fetch forward, false for fetch backward
2809 </para>
2810 </listitem>
2811 </varlistentry>
2812
2813 <varlistentry>
2814 <term><literal>long <parameter>count</parameter></literal></term>
2815 <listitem>
2816 <para>
2817 maximum number of rows to fetch
2818 </para>
2819 </listitem>
2820 </varlistentry>
2821 </variablelist>
2822 </refsect1>
2823
2824 <refsect1>
2825 <title>Return Value</title>
2826
2827 <para>
2828 <varname>SPI_processed</varname> and
2829 <varname>SPI_tuptable</varname> are set as in
2830 <function>SPI_execute</function> if successful.
2831 </para>
2832 </refsect1>
2833
2834 <refsect1>
2835 <title>Notes</title>
2836
2837 <para>
2838 Fetching backward may fail if the cursor's plan was not created
2839 with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
2840 </para>
2841 </refsect1>
2842 </refentry>
2843
2844 <!-- *********************************************** -->
2845
2846 <refentry id="spi-spi-cursor-move">
2847 <indexterm><primary>SPI_cursor_move</primary></indexterm>
2848
2849 <refmeta>
2850 <refentrytitle>SPI_cursor_move</refentrytitle>
2851 <manvolnum>3</manvolnum>
2852 </refmeta>
2853
2854 <refnamediv>
2855 <refname>SPI_cursor_move</refname>
2856 <refpurpose>move a cursor</refpurpose>
2857 </refnamediv>
2858
2859 <refsynopsisdiv>
2860 <synopsis>
2861 void SPI_cursor_move(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, long <parameter>count</parameter>)
2862 </synopsis>
2863 </refsynopsisdiv>
2864
2865 <refsect1>
2866 <title>Description</title>
2867
2868 <para>
2869 <function>SPI_cursor_move</function> skips over some number of rows
2870 in a cursor. This is equivalent to a subset of the SQL command
2871 <command>MOVE</command> (see <function>SPI_scroll_cursor_move</function>
2872 for more functionality).
2873 </para>
2874 </refsect1>
2875
2876 <refsect1>
2877 <title>Arguments</title>
2878
2879 <variablelist>
2880 <varlistentry>
2881 <term><literal>Portal <parameter>portal</parameter></literal></term>
2882 <listitem>
2883 <para>
2884 portal containing the cursor
2885 </para>
2886 </listitem>
2887 </varlistentry>
2888
2889 <varlistentry>
2890 <term><literal>bool <parameter>forward</parameter></literal></term>
2891 <listitem>
2892 <para>
2893 true for move forward, false for move backward
2894 </para>
2895 </listitem>
2896 </varlistentry>
2897
2898 <varlistentry>
2899 <term><literal>long <parameter>count</parameter></literal></term>
2900 <listitem>
2901 <para>
2902 maximum number of rows to move
2903 </para>
2904 </listitem>
2905 </varlistentry>
2906 </variablelist>
2907 </refsect1>
2908
2909 <refsect1>
2910 <title>Notes</title>
2911
2912 <para>
2913 Moving backward may fail if the cursor's plan was not created
2914 with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
2915 </para>
2916 </refsect1>
2917 </refentry>
2918
2919 <!-- *********************************************** -->
2920
2921 <refentry id="spi-spi-scroll-cursor-fetch">
2922 <indexterm><primary>SPI_scroll_cursor_fetch</primary></indexterm>
2923
2924 <refmeta>
2925 <refentrytitle>SPI_scroll_cursor_fetch</refentrytitle>
2926 <manvolnum>3</manvolnum>
2927 </refmeta>
2928
2929 <refnamediv>
2930 <refname>SPI_scroll_cursor_fetch</refname>
2931 <refpurpose>fetch some rows from a cursor</refpurpose>
2932 </refnamediv>
2933
2934 <refsynopsisdiv>
2935 <synopsis>
2936 void SPI_scroll_cursor_fetch(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>,
2937 long <parameter>count</parameter>)
2938 </synopsis>
2939 </refsynopsisdiv>
2940
2941 <refsect1>
2942 <title>Description</title>
2943
2944 <para>
2945 <function>SPI_scroll_cursor_fetch</function> fetches some rows from a
2946 cursor. This is equivalent to the SQL command <command>FETCH</command>.
2947 </para>
2948 </refsect1>
2949
2950 <refsect1>
2951 <title>Arguments</title>
2952
2953 <variablelist>
2954 <varlistentry>
2955 <term><literal>Portal <parameter>portal</parameter></literal></term>
2956 <listitem>
2957 <para>
2958 portal containing the cursor
2959 </para>
2960 </listitem>
2961 </varlistentry>
2962
2963 <varlistentry>
2964 <term><literal>FetchDirection <parameter>direction</parameter></literal></term>
2965 <listitem>
2966 <para>
2967 one of <symbol>FETCH_FORWARD</symbol>,
2968 <symbol>FETCH_BACKWARD</symbol>,
2969 <symbol>FETCH_ABSOLUTE</symbol> or
2970 <symbol>FETCH_RELATIVE</symbol>
2971 </para>
2972 </listitem>
2973 </varlistentry>
2974
2975 <varlistentry>
2976 <term><literal>long <parameter>count</parameter></literal></term>
2977 <listitem>
2978 <para>
2979 number of rows to fetch for
2980 <symbol>FETCH_FORWARD</symbol> or
2981 <symbol>FETCH_BACKWARD</symbol>; absolute row number to fetch for
2982 <symbol>FETCH_ABSOLUTE</symbol>; or relative row number to fetch for
2983 <symbol>FETCH_RELATIVE</symbol>
2984 </para>
2985 </listitem>
2986 </varlistentry>
2987 </variablelist>
2988 </refsect1>
2989
2990 <refsect1>
2991 <title>Return Value</title>
2992
2993 <para>
2994 <varname>SPI_processed</varname> and
2995 <varname>SPI_tuptable</varname> are set as in
2996 <function>SPI_execute</function> if successful.
2997 </para>
2998 </refsect1>
2999
3000 <refsect1>
3001 <title>Notes</title>
3002
3003 <para>
3004 See the SQL <xref linkend="sql-fetch"/> command
3005 for details of the interpretation of the
3006 <parameter>direction</parameter> and
3007 <parameter>count</parameter> parameters.
3008 </para>
3009
3010 <para>
3011 Direction values other than <symbol>FETCH_FORWARD</symbol>
3012 may fail if the cursor's plan was not created
3013 with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
3014 </para>
3015 </refsect1>
3016 </refentry>
3017
3018 <!-- *********************************************** -->
3019
3020 <refentry id="spi-spi-scroll-cursor-move">
3021 <indexterm><primary>SPI_scroll_cursor_move</primary></indexterm>
3022
3023 <refmeta>
3024 <refentrytitle>SPI_scroll_cursor_move</refentrytitle>
3025 <manvolnum>3</manvolnum>
3026 </refmeta>
3027
3028 <refnamediv>
3029 <refname>SPI_scroll_cursor_move</refname>
3030 <refpurpose>move a cursor</refpurpose>
3031 </refnamediv>
3032
3033 <refsynopsisdiv>
3034 <synopsis>
3035 void SPI_scroll_cursor_move(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>,
3036 long <parameter>count</parameter>)
3037 </synopsis>
3038 </refsynopsisdiv>
3039
3040 <refsect1>
3041 <title>Description</title>
3042
3043 <para>
3044 <function>SPI_scroll_cursor_move</function> skips over some number of rows
3045 in a cursor. This is equivalent to the SQL command
3046 <command>MOVE</command>.
3047 </para>
3048 </refsect1>
3049
3050 <refsect1>
3051 <title>Arguments</title>
3052
3053 <variablelist>
3054 <varlistentry>
3055 <term><literal>Portal <parameter>portal</parameter></literal></term>
3056 <listitem>
3057 <para>
3058 portal containing the cursor
3059 </para>
3060 </listitem>
3061 </varlistentry>
3062
3063 <varlistentry>
3064 <term><literal>FetchDirection <parameter>direction</parameter></literal></term>
3065 <listitem>
3066 <para>
3067 one of <symbol>FETCH_FORWARD</symbol>,
3068 <symbol>FETCH_BACKWARD</symbol>,
3069 <symbol>FETCH_ABSOLUTE</symbol> or
3070 <symbol>FETCH_RELATIVE</symbol>
3071 </para>
3072 </listitem>
3073 </varlistentry>
3074
3075 <varlistentry>
3076 <term><literal>long <parameter>count</parameter></literal></term>
3077 <listitem>
3078 <para>
3079 number of rows to move for
3080 <symbol>FETCH_FORWARD</symbol> or
3081 <symbol>FETCH_BACKWARD</symbol>; absolute row number to move to for
3082 <symbol>FETCH_ABSOLUTE</symbol>; or relative row number to move to for
3083 <symbol>FETCH_RELATIVE</symbol>
3084 </para>
3085 </listitem>
3086 </varlistentry>
3087 </variablelist>
3088 </refsect1>
3089
3090 <refsect1>
3091 <title>Return Value</title>
3092
3093 <para>
3094 <varname>SPI_processed</varname> is set as in
3095 <function>SPI_execute</function> if successful.
3096 <varname>SPI_tuptable</varname> is set to <symbol>NULL</symbol>, since
3097 no rows are returned by this function.
3098 </para>
3099 </refsect1>
3100
3101 <refsect1>
3102 <title>Notes</title>
3103
3104 <para>
3105 See the SQL <xref linkend="sql-fetch"/> command
3106 for details of the interpretation of the
3107 <parameter>direction</parameter> and
3108 <parameter>count</parameter> parameters.
3109 </para>
3110
3111 <para>
3112 Direction values other than <symbol>FETCH_FORWARD</symbol>
3113 may fail if the cursor's plan was not created
3114 with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
3115 </para>
3116 </refsect1>
3117 </refentry>
3118
3119 <!-- *********************************************** -->
3120
3121 <refentry id="spi-spi-cursor-close">
3122 <indexterm><primary>SPI_cursor_close</primary></indexterm>
3123
3124 <refmeta>
3125 <refentrytitle>SPI_cursor_close</refentrytitle>
3126 <manvolnum>3</manvolnum>
3127 </refmeta>
3128
3129 <refnamediv>
3130 <refname>SPI_cursor_close</refname>
3131 <refpurpose>close a cursor</refpurpose>
3132 </refnamediv>
3133
3134 <refsynopsisdiv>
3135 <synopsis>
3136 void SPI_cursor_close(Portal <parameter>portal</parameter>)
3137 </synopsis>
3138 </refsynopsisdiv>
3139
3140 <refsect1>
3141 <title>Description</title>
3142
3143 <para>
3144 <function>SPI_cursor_close</function> closes a previously created
3145 cursor and releases its portal storage.
3146 </para>
3147
3148 <para>
3149 All open cursors are closed automatically at the end of a
3150 transaction. <function>SPI_cursor_close</function> need only be
3151 invoked if it is desirable to release resources sooner.
3152 </para>
3153 </refsect1>
3154
3155 <refsect1>
3156 <title>Arguments</title>
3157
3158 <variablelist>
3159 <varlistentry>
3160 <term><literal>Portal <parameter>portal</parameter></literal></term>
3161 <listitem>
3162 <para>
3163 portal containing the cursor
3164 </para>
3165 </listitem>
3166 </varlistentry>
3167 </variablelist>
3168 </refsect1>
3169 </refentry>
3170
3171 <!-- *********************************************** -->
3172
3173 <refentry id="spi-spi-keepplan">
3174 <indexterm><primary>SPI_keepplan</primary></indexterm>
3175
3176 <refmeta>
3177 <refentrytitle>SPI_keepplan</refentrytitle>
3178 <manvolnum>3</manvolnum>
3179 </refmeta>
3180
3181 <refnamediv>
3182 <refname>SPI_keepplan</refname>
3183 <refpurpose>save a prepared statement</refpurpose>
3184 </refnamediv>
3185
3186 <refsynopsisdiv>
3187 <synopsis>
3188 int SPI_keepplan(SPIPlanPtr <parameter>plan</parameter>)
3189 </synopsis>
3190 </refsynopsisdiv>
3191
3192 <refsect1>
3193 <title>Description</title>
3194
3195 <para>
3196 <function>SPI_keepplan</function> saves a passed statement (prepared by
3197 <function>SPI_prepare</function>) so that it will not be freed
3198 by <function>SPI_finish</function> nor by the transaction manager.
3199 This gives you the ability to reuse prepared statements in the subsequent
3200 invocations of your C function in the current session.
3201 </para>
3202 </refsect1>
3203
3204 <refsect1>
3205 <title>Arguments</title>
3206
3207 <variablelist>
3208 <varlistentry>
3209 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
3210 <listitem>
3211 <para>
3212 the prepared statement to be saved
3213 </para>
3214 </listitem>
3215 </varlistentry>
3216 </variablelist>
3217 </refsect1>
3218
3219 <refsect1>
3220 <title>Return Value</title>
3221
3222 <para>
3223 0 on success;
3224 <symbol>SPI_ERROR_ARGUMENT</symbol> if <parameter>plan</parameter>
3225 is <symbol>NULL</symbol> or invalid
3226 </para>
3227 </refsect1>
3228
3229 <refsect1>
3230 <title>Notes</title>
3231
3232 <para>
3233 The passed-in statement is relocated to permanent storage by means
3234 of pointer adjustment (no data copying is required). If you later
3235 wish to delete it, use <function>SPI_freeplan</function> on it.
3236 </para>
3237 </refsect1>
3238 </refentry>
3239
3240 <!-- *********************************************** -->
3241
3242 <refentry id="spi-spi-saveplan">
3243 <indexterm><primary>SPI_saveplan</primary></indexterm>
3244
3245 <refmeta>
3246 <refentrytitle>SPI_saveplan</refentrytitle>
3247 <manvolnum>3</manvolnum>
3248 </refmeta>
3249
3250 <refnamediv>
3251 <refname>SPI_saveplan</refname>
3252 <refpurpose>save a prepared statement</refpurpose>
3253 </refnamediv>
3254
3255 <refsynopsisdiv>
3256 <synopsis>
3257 SPIPlanPtr SPI_saveplan(SPIPlanPtr <parameter>plan</parameter>)
3258 </synopsis>
3259 </refsynopsisdiv>
3260
3261 <refsect1>
3262 <title>Description</title>
3263
3264 <para>
3265 <function>SPI_saveplan</function> copies a passed statement (prepared by
3266 <function>SPI_prepare</function>) into memory that will not be freed
3267 by <function>SPI_finish</function> nor by the transaction manager,
3268 and returns a pointer to the copied statement. This gives you the
3269 ability to reuse prepared statements in the subsequent invocations of
3270 your C function in the current session.
3271 </para>
3272 </refsect1>
3273
3274 <refsect1>
3275 <title>Arguments</title>
3276
3277 <variablelist>
3278 <varlistentry>
3279 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
3280 <listitem>
3281 <para>
3282 the prepared statement to be saved
3283 </para>
3284 </listitem>
3285 </varlistentry>
3286 </variablelist>
3287 </refsect1>
3288
3289 <refsect1>
3290 <title>Return Value</title>
3291
3292 <para>
3293 Pointer to the copied statement; or <symbol>NULL</symbol> if unsuccessful.
3294 On error, <varname>SPI_result</varname> is set thus:
3295
3296 <variablelist>
3297 <varlistentry>
3298 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
3299 <listitem>
3300 <para>
3301 if <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid
3302 </para>
3303 </listitem>
3304 </varlistentry>
3305
3306 <varlistentry>
3307 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
3308 <listitem>
3309 <para>
3310 if called from an unconnected C function
3311 </para>
3312 </listitem>
3313 </varlistentry>
3314 </variablelist>
3315 </para>
3316 </refsect1>
3317
3318 <refsect1>
3319 <title>Notes</title>
3320
3321 <para>
3322 The originally passed-in statement is not freed, so you might wish to do
3323 <function>SPI_freeplan</function> on it to avoid leaking memory
3324 until <function>SPI_finish</function>.
3325 </para>
3326
3327 <para>
3328 In most cases, <function>SPI_keepplan</function> is preferred to this
3329 function, since it accomplishes largely the same result without needing
3330 to physically copy the prepared statement's data structures.
3331 </para>
3332 </refsect1>
3333 </refentry>
3334
3335 <!-- *********************************************** -->
3336
3337 <refentry id="spi-spi-register-relation">
3338 <indexterm><primary>SPI_register_relation</primary></indexterm>
3339
3340 <indexterm>
3341 <primary>ephemeral named relation</primary>
3342 <secondary>registering with SPI</secondary>
3343 </indexterm>
3344
3345 <refmeta>
3346 <refentrytitle>SPI_register_relation</refentrytitle>
3347 <manvolnum>3</manvolnum>
3348 </refmeta>
3349
3350 <refnamediv>
3351 <refname>SPI_register_relation</refname>
3352 <refpurpose>make an ephemeral named relation available by name in SPI queries</refpurpose>
3353 </refnamediv>
3354
3355 <refsynopsisdiv>
3356 <synopsis>
3357 int SPI_register_relation(EphemeralNamedRelation <parameter>enr</parameter>)
3358 </synopsis>
3359 </refsynopsisdiv>
3360
3361 <refsect1>
3362 <title>Description</title>
3363
3364 <para>
3365 <function>SPI_register_relation</function> makes an ephemeral named
3366 relation, with associated information, available to queries planned and
3367 executed through the current SPI connection.
3368 </para>
3369 </refsect1>
3370
3371 <refsect1>
3372 <title>Arguments</title>
3373
3374 <variablelist>
3375 <varlistentry>
3376 <term><literal>EphemeralNamedRelation <parameter>enr</parameter></literal></term>
3377 <listitem>
3378 <para>
3379 the ephemeral named relation registry entry
3380 </para>
3381 </listitem>
3382 </varlistentry>
3383 </variablelist>
3384 </refsect1>
3385
3386 <refsect1>
3387 <title>Return Value</title>
3388
3389 <para>
3390 If the execution of the command was successful then the following
3391 (nonnegative) value will be returned:
3392
3393 <variablelist>
3394 <varlistentry>
3395 <term><symbol>SPI_OK_REL_REGISTER</symbol></term>
3396 <listitem>
3397 <para>
3398 if the relation has been successfully registered by name
3399 </para>
3400 </listitem>
3401 </varlistentry>
3402 </variablelist>
3403 </para>
3404
3405 <para>
3406 On error, one of the following negative values is returned:
3407
3408 <variablelist>
3409 <varlistentry>
3410 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
3411 <listitem>
3412 <para>
3413 if <parameter>enr</parameter> is <symbol>NULL</symbol> or its
3414 <varname>name</varname> field is <symbol>NULL</symbol>
3415 </para>
3416 </listitem>
3417 </varlistentry>
3418
3419 <varlistentry>
3420 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
3421 <listitem>
3422 <para>
3423 if called from an unconnected C function
3424 </para>
3425 </listitem>
3426 </varlistentry>
3427
3428 <varlistentry>
3429 <term><symbol>SPI_ERROR_REL_DUPLICATE</symbol></term>
3430 <listitem>
3431 <para>
3432 if the name specified in the <varname>name</varname> field of
3433 <parameter>enr</parameter> is already registered for this connection
3434 </para>
3435 </listitem>
3436 </varlistentry>
3437 </variablelist>
3438 </para>
3439 </refsect1>
3440 </refentry>
3441
3442 <!-- *********************************************** -->
3443
3444 <refentry id="spi-spi-unregister-relation">
3445 <indexterm><primary>SPI_unregister_relation</primary></indexterm>
3446
3447 <indexterm>
3448 <primary>ephemeral named relation</primary>
3449 <secondary>unregistering from SPI</secondary>
3450 </indexterm>
3451
3452 <refmeta>
3453 <refentrytitle>SPI_unregister_relation</refentrytitle>
3454 <manvolnum>3</manvolnum>
3455 </refmeta>
3456
3457 <refnamediv>
3458 <refname>SPI_unregister_relation</refname>
3459 <refpurpose>remove an ephemeral named relation from the registry</refpurpose>
3460 </refnamediv>
3461
3462 <refsynopsisdiv>
3463 <synopsis>
3464 int SPI_unregister_relation(const char * <parameter>name</parameter>)
3465 </synopsis>
3466 </refsynopsisdiv>
3467
3468 <refsect1>
3469 <title>Description</title>
3470
3471 <para>
3472 <function>SPI_unregister_relation</function> removes an ephemeral named
3473 relation from the registry for the current connection.
3474 </para>
3475 </refsect1>
3476
3477 <refsect1>
3478 <title>Arguments</title>
3479
3480 <variablelist>
3481 <varlistentry>
3482 <term><literal>const char * <parameter>name</parameter></literal></term>
3483 <listitem>
3484 <para>
3485 the relation registry entry name
3486 </para>
3487 </listitem>
3488 </varlistentry>
3489 </variablelist>
3490 </refsect1>
3491
3492 <refsect1>
3493 <title>Return Value</title>
3494
3495 <para>
3496 If the execution of the command was successful then the following
3497 (nonnegative) value will be returned:
3498
3499 <variablelist>
3500 <varlistentry>
3501 <term><symbol>SPI_OK_REL_UNREGISTER</symbol></term>
3502 <listitem>
3503 <para>
3504 if the tuplestore has been successfully removed from the registry
3505 </para>
3506 </listitem>
3507 </varlistentry>
3508 </variablelist>
3509 </para>
3510
3511 <para>
3512 On error, one of the following negative values is returned:
3513
3514 <variablelist>
3515 <varlistentry>
3516 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
3517 <listitem>
3518 <para>
3519 if <parameter>name</parameter> is <symbol>NULL</symbol>
3520 </para>
3521 </listitem>
3522 </varlistentry>
3523
3524 <varlistentry>
3525 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
3526 <listitem>
3527 <para>
3528 if called from an unconnected C function
3529 </para>
3530 </listitem>
3531 </varlistentry>
3532
3533 <varlistentry>
3534 <term><symbol>SPI_ERROR_REL_NOT_FOUND</symbol></term>
3535 <listitem>
3536 <para>
3537 if <parameter>name</parameter> is not found in the registry for the
3538 current connection
3539 </para>
3540 </listitem>
3541 </varlistentry>
3542 </variablelist>
3543 </para>
3544 </refsect1>
3545 </refentry>
3546
3547 <!-- *********************************************** -->
3548
3549 <refentry id="spi-spi-register-trigger-data">
3550 <indexterm><primary>SPI_register_trigger_data</primary></indexterm>
3551
3552 <indexterm>
3553 <primary>ephemeral named relation</primary>
3554 <secondary>registering with SPI</secondary>
3555 </indexterm>
3556
3557 <indexterm>
3558 <primary>transition tables</primary>
3559 <secondary>implementation in PLs</secondary>
3560 </indexterm>
3561
3562 <refmeta>
3563 <refentrytitle>SPI_register_trigger_data</refentrytitle>
3564 <manvolnum>3</manvolnum>
3565 </refmeta>
3566
3567 <refnamediv>
3568 <refname>SPI_register_trigger_data</refname>
3569 <refpurpose>make ephemeral trigger data available in SPI queries</refpurpose>
3570 </refnamediv>
3571
3572 <refsynopsisdiv>
3573 <synopsis>
3574 int SPI_register_trigger_data(TriggerData *<parameter>tdata</parameter>)
3575 </synopsis>
3576 </refsynopsisdiv>
3577
3578 <refsect1>
3579 <title>Description</title>
3580
3581 <para>
3582 <function>SPI_register_trigger_data</function> makes any ephemeral
3583 relations captured by a trigger available to queries planned and executed
3584 through the current SPI connection. Currently, this means the transition
3585 tables captured by an <literal>AFTER</literal> trigger defined with a
3586 <literal>REFERENCING OLD/NEW TABLE AS</literal> ... clause. This function
3587 should be called by a PL trigger handler function after connecting.
3588 </para>
3589 </refsect1>
3590
3591 <refsect1>
3592 <title>Arguments</title>
3593
3594 <variablelist>
3595 <varlistentry>
3596 <term><literal>TriggerData *<parameter>tdata</parameter></literal></term>
3597 <listitem>
3598 <para>
3599 the <structname>TriggerData</structname> object passed to a trigger
3600 handler function as <literal>fcinfo->context</literal>
3601 </para>
3602 </listitem>
3603 </varlistentry>
3604 </variablelist>
3605 </refsect1>
3606
3607 <refsect1>
3608 <title>Return Value</title>
3609
3610 <para>
3611 If the execution of the command was successful then the following
3612 (nonnegative) value will be returned:
3613
3614 <variablelist>
3615 <varlistentry>
3616 <term><symbol>SPI_OK_TD_REGISTER</symbol></term>
3617 <listitem>
3618 <para>
3619 if the captured trigger data (if any) has been successfully registered
3620 </para>
3621 </listitem>
3622 </varlistentry>
3623 </variablelist>
3624 </para>
3625
3626 <para>
3627 On error, one of the following negative values is returned:
3628
3629 <variablelist>
3630 <varlistentry>
3631 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
3632 <listitem>
3633 <para>
3634 if <parameter>tdata</parameter> is <symbol>NULL</symbol>
3635 </para>
3636 </listitem>
3637 </varlistentry>
3638
3639 <varlistentry>
3640 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
3641 <listitem>
3642 <para>
3643 if called from an unconnected C function
3644 </para>
3645 </listitem>
3646 </varlistentry>
3647
3648 <varlistentry>
3649 <term><symbol>SPI_ERROR_REL_DUPLICATE</symbol></term>
3650 <listitem>
3651 <para>
3652 if the name of any trigger data transient relation is already
3653 registered for this connection
3654 </para>
3655 </listitem>
3656 </varlistentry>
3657 </variablelist>
3658 </para>
3659 </refsect1>
3660 </refentry>
3661
3662 <!-- *********************************************** -->
3663
3664 </sect1>
3665
3666 <sect1 id="spi-interface-support">
3667 <title>Interface Support Functions</title>
3668
3669 <para>
3670 The functions described here provide an interface for extracting
3671 information from result sets returned by <function>SPI_execute</function> and
3672 other SPI functions.
3673 </para>
3674
3675 <para>
3676 All functions described in this section can be used by both
3677 connected and unconnected C functions.
3678 </para>
3679
3680 <!-- *********************************************** -->
3681
3682 <refentry id="spi-spi-fname">
3683 <indexterm><primary>SPI_fname</primary></indexterm>
3684
3685 <refmeta>
3686 <refentrytitle>SPI_fname</refentrytitle>
3687 <manvolnum>3</manvolnum>
3688 </refmeta>
3689
3690 <refnamediv>
3691 <refname>SPI_fname</refname>
3692 <refpurpose>determine the column name for the specified column number</refpurpose>
3693 </refnamediv>
3694
3695 <refsynopsisdiv>
3696 <synopsis>
3697 char * SPI_fname(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
3698 </synopsis>
3699 </refsynopsisdiv>
3700
3701 <refsect1>
3702 <title>Description</title>
3703
3704 <para>
3705 <function>SPI_fname</function> returns a copy of the column name of the
3706 specified column. (You can use <function>pfree</function> to
3707 release the copy of the name when you don't need it anymore.)
3708 </para>
3709 </refsect1>
3710
3711 <refsect1>
3712 <title>Arguments</title>
3713
3714 <variablelist>
3715 <varlistentry>
3716 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
3717 <listitem>
3718 <para>
3719 input row description
3720 </para>
3721 </listitem>
3722 </varlistentry>
3723
3724 <varlistentry>
3725 <term><literal>int <parameter>colnumber</parameter></literal></term>
3726 <listitem>
3727 <para>
3728 column number (count starts at 1)
3729 </para>
3730 </listitem>
3731 </varlistentry>
3732 </variablelist>
3733 </refsect1>
3734
3735 <refsect1>
3736 <title>Return Value</title>
3737
3738 <para>
3739 The column name; <symbol>NULL</symbol> if
3740 <parameter>colnumber</parameter> is out of range.
3741 <varname>SPI_result</varname> set to
3742 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
3743 </para>
3744 </refsect1>
3745 </refentry>
3746
3747 <!-- *********************************************** -->
3748
3749 <refentry id="spi-spi-fnumber">
3750 <indexterm><primary>SPI_fnumber</primary></indexterm>
3751
3752 <refmeta>
3753 <refentrytitle>SPI_fnumber</refentrytitle>
3754 <manvolnum>3</manvolnum>
3755 </refmeta>
3756
3757 <refnamediv>
3758 <refname>SPI_fnumber</refname>
3759 <refpurpose>determine the column number for the specified column name</refpurpose>
3760 </refnamediv>
3761
3762 <refsynopsisdiv>
3763 <synopsis>
3764 int SPI_fnumber(TupleDesc <parameter>rowdesc</parameter>, const char * <parameter>colname</parameter>)
3765 </synopsis>
3766 </refsynopsisdiv>
3767
3768 <refsect1>
3769 <title>Description</title>
3770
3771 <para>
3772 <function>SPI_fnumber</function> returns the column number for the
3773 column with the specified name.
3774 </para>
3775
3776 <para>
3777 If <parameter>colname</parameter> refers to a system column (e.g.,
3778 <literal>ctid</literal>) then the appropriate negative column number will
3779 be returned. The caller should be careful to test the return value
3780 for exact equality to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> to
3781 detect an error; testing the result for less than or equal to 0 is
3782 not correct unless system columns should be rejected.
3783 </para>
3784 </refsect1>
3785
3786 <refsect1>
3787 <title>Arguments</title>
3788
3789 <variablelist>
3790 <varlistentry>
3791 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
3792 <listitem>
3793 <para>
3794 input row description
3795 </para>
3796 </listitem>
3797 </varlistentry>
3798
3799 <varlistentry>
3800 <term><literal>const char * <parameter>colname</parameter></literal></term>
3801 <listitem>
3802 <para>
3803 column name
3804 </para>
3805 </listitem>
3806 </varlistentry>
3807 </variablelist>
3808 </refsect1>
3809
3810 <refsect1>
3811 <title>Return Value</title>
3812
3813 <para>
3814 Column number (count starts at 1 for user-defined columns), or
3815 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> if the named column was not
3816 found.
3817 </para>
3818 </refsect1>
3819 </refentry>
3820
3821 <!-- *********************************************** -->
3822
3823 <refentry id="spi-spi-getvalue">
3824 <indexterm><primary>SPI_getvalue</primary></indexterm>
3825
3826 <refmeta>
3827 <refentrytitle>SPI_getvalue</refentrytitle>
3828 <manvolnum>3</manvolnum>
3829 </refmeta>
3830
3831 <refnamediv>
3832 <refname>SPI_getvalue</refname>
3833 <refpurpose>return the string value of the specified column</refpurpose>
3834 </refnamediv>
3835
3836 <refsynopsisdiv>
3837 <synopsis>
3838 char * SPI_getvalue(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
3839 </synopsis>
3840 </refsynopsisdiv>
3841
3842 <refsect1>
3843 <title>Description</title>
3844
3845 <para>
3846 <function>SPI_getvalue</function> returns the string representation
3847 of the value of the specified column.
3848 </para>
3849
3850 <para>
3851 The result is returned in memory allocated using
3852 <function>palloc</function>. (You can use
3853 <function>pfree</function> to release the memory when you don't
3854 need it anymore.)
3855 </para>
3856 </refsect1>
3857
3858 <refsect1>
3859 <title>Arguments</title>
3860
3861 <variablelist>
3862 <varlistentry>
3863 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
3864 <listitem>
3865 <para>
3866 input row to be examined
3867 </para>
3868 </listitem>
3869 </varlistentry>
3870
3871 <varlistentry>
3872 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
3873 <listitem>
3874 <para>
3875 input row description
3876 </para>
3877 </listitem>
3878 </varlistentry>
3879
3880 <varlistentry>
3881 <term><literal>int <parameter>colnumber</parameter></literal></term>
3882 <listitem>
3883 <para>
3884 column number (count starts at 1)
3885 </para>
3886 </listitem>
3887 </varlistentry>
3888 </variablelist>
3889 </refsect1>
3890
3891 <refsect1>
3892 <title>Return Value</title>
3893
3894 <para>
3895 Column value, or <symbol>NULL</symbol> if the column is null,
3896 <parameter>colnumber</parameter> is out of range
3897 (<varname>SPI_result</varname> is set to
3898 <symbol>SPI_ERROR_NOATTRIBUTE</symbol>), or no output function is
3899 available (<varname>SPI_result</varname> is set to
3900 <symbol>SPI_ERROR_NOOUTFUNC</symbol>).
3901 </para>
3902 </refsect1>
3903 </refentry>
3904
3905 <!-- *********************************************** -->
3906
3907 <refentry id="spi-spi-getbinval">
3908 <indexterm><primary>SPI_getbinval</primary></indexterm>
3909
3910 <refmeta>
3911 <refentrytitle>SPI_getbinval</refentrytitle>
3912 <manvolnum>3</manvolnum>
3913 </refmeta>
3914
3915 <refnamediv>
3916 <refname>SPI_getbinval</refname>
3917 <refpurpose>return the binary value of the specified column</refpurpose>
3918 </refnamediv>
3919
3920 <refsynopsisdiv>
3921 <synopsis>
3922 Datum SPI_getbinval(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>,
3923 bool * <parameter>isnull</parameter>)
3924 </synopsis>
3925 </refsynopsisdiv>
3926
3927 <refsect1>
3928 <title>Description</title>
3929
3930 <para>
3931 <function>SPI_getbinval</function> returns the value of the
3932 specified column in the internal form (as type <type>Datum</type>).
3933 </para>
3934
3935 <para>
3936 This function does not allocate new space for the datum. In the
3937 case of a pass-by-reference data type, the return value will be a
3938 pointer into the passed row.
3939 </para>
3940 </refsect1>
3941
3942 <refsect1>
3943 <title>Arguments</title>
3944
3945 <variablelist>
3946 <varlistentry>
3947 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
3948 <listitem>
3949 <para>
3950 input row to be examined
3951 </para>
3952 </listitem>
3953 </varlistentry>
3954
3955 <varlistentry>
3956 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
3957 <listitem>
3958 <para>
3959 input row description
3960 </para>
3961 </listitem>
3962 </varlistentry>
3963
3964 <varlistentry>
3965 <term><literal>int <parameter>colnumber</parameter></literal></term>
3966 <listitem>
3967 <para>
3968 column number (count starts at 1)
3969 </para>
3970 </listitem>
3971 </varlistentry>
3972
3973 <varlistentry>
3974 <term><literal>bool * <parameter>isnull</parameter></literal></term>
3975 <listitem>
3976 <para>
3977 flag for a null value in the column
3978 </para>
3979 </listitem>
3980 </varlistentry>
3981 </variablelist>
3982 </refsect1>
3983
3984 <refsect1>
3985 <title>Return Value</title>
3986
3987 <para>
3988 The binary value of the column is returned. The variable pointed
3989 to by <parameter>isnull</parameter> is set to true if the column is
3990 null, else to false.
3991 </para>
3992
3993 <para>
3994 <varname>SPI_result</varname> is set to
3995 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
3996 </para>
3997 </refsect1>
3998 </refentry>
3999
4000 <!-- *********************************************** -->
4001
4002 <refentry id="spi-spi-gettype">
4003 <indexterm><primary>SPI_gettype</primary></indexterm>
4004
4005 <refmeta>
4006 <refentrytitle>SPI_gettype</refentrytitle>
4007 <manvolnum>3</manvolnum>
4008 </refmeta>
4009
4010 <refnamediv>
4011 <refname>SPI_gettype</refname>
4012 <refpurpose>return the data type name of the specified column</refpurpose>
4013 </refnamediv>
4014
4015 <refsynopsisdiv>
4016 <synopsis>
4017 char * SPI_gettype(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
4018 </synopsis>
4019 </refsynopsisdiv>
4020
4021 <refsect1>
4022 <title>Description</title>
4023
4024 <para>
4025 <function>SPI_gettype</function> returns a copy of the data type name of the
4026 specified column. (You can use <function>pfree</function> to
4027 release the copy of the name when you don't need it anymore.)
4028 </para>
4029 </refsect1>
4030
4031 <refsect1>
4032 <title>Arguments</title>
4033
4034 <variablelist>
4035 <varlistentry>
4036 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
4037 <listitem>
4038 <para>
4039 input row description
4040 </para>
4041 </listitem>
4042 </varlistentry>
4043
4044 <varlistentry>
4045 <term><literal>int <parameter>colnumber</parameter></literal></term>
4046 <listitem>
4047 <para>
4048 column number (count starts at 1)
4049 </para>
4050 </listitem>
4051 </varlistentry>
4052 </variablelist>
4053 </refsect1>
4054
4055 <refsect1>
4056 <title>Return Value</title>
4057
4058 <para>
4059 The data type name of the specified column, or
4060 <symbol>NULL</symbol> on error. <varname>SPI_result</varname> is
4061 set to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
4062 </para>
4063 </refsect1>
4064 </refentry>
4065
4066 <!-- *********************************************** -->
4067
4068 <refentry id="spi-spi-gettypeid">
4069 <indexterm><primary>SPI_gettypeid</primary></indexterm>
4070
4071 <refmeta>
4072 <refentrytitle>SPI_gettypeid</refentrytitle>
4073 <manvolnum>3</manvolnum>
4074 </refmeta>
4075
4076 <refnamediv>
4077 <refname>SPI_gettypeid</refname>
4078 <refpurpose>return the data type <acronym>OID</acronym> of the specified column</refpurpose>
4079 </refnamediv>
4080
4081 <refsynopsisdiv>
4082 <synopsis>
4083 Oid SPI_gettypeid(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
4084 </synopsis>
4085 </refsynopsisdiv>
4086
4087 <refsect1>
4088 <title>Description</title>
4089
4090 <para>
4091 <function>SPI_gettypeid</function> returns the
4092 <acronym>OID</acronym> of the data type of the specified column.
4093 </para>
4094 </refsect1>
4095
4096 <refsect1>
4097 <title>Arguments</title>
4098
4099 <variablelist>
4100 <varlistentry>
4101 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
4102 <listitem>
4103 <para>
4104 input row description
4105 </para>
4106 </listitem>
4107 </varlistentry>
4108
4109 <varlistentry>
4110 <term><literal>int <parameter>colnumber</parameter></literal></term>
4111 <listitem>
4112 <para>
4113 column number (count starts at 1)
4114 </para>
4115 </listitem>
4116 </varlistentry>
4117 </variablelist>
4118 </refsect1>
4119
4120 <refsect1>
4121 <title>Return Value</title>
4122
4123 <para>
4124 The <acronym>OID</acronym> of the data type of the specified column
4125 or <symbol>InvalidOid</symbol> on error. On error,
4126 <varname>SPI_result</varname> is set to
4127 <symbol>SPI_ERROR_NOATTRIBUTE</symbol>.
4128 </para>
4129 </refsect1>
4130 </refentry>
4131
4132 <!-- *********************************************** -->
4133
4134 <refentry id="spi-spi-getrelname">
4135 <indexterm><primary>SPI_getrelname</primary></indexterm>
4136
4137 <refmeta>
4138 <refentrytitle>SPI_getrelname</refentrytitle>
4139 <manvolnum>3</manvolnum>
4140 </refmeta>
4141
4142 <refnamediv>
4143 <refname>SPI_getrelname</refname>
4144 <refpurpose>return the name of the specified relation</refpurpose>
4145 </refnamediv>
4146
4147 <refsynopsisdiv>
4148 <synopsis>
4149 char * SPI_getrelname(Relation <parameter>rel</parameter>)
4150 </synopsis>
4151 </refsynopsisdiv>
4152
4153 <refsect1>
4154 <title>Description</title>
4155
4156 <para>
4157 <function>SPI_getrelname</function> returns a copy of the name of the
4158 specified relation. (You can use <function>pfree</function> to
4159 release the copy of the name when you don't need it anymore.)
4160 </para>
4161 </refsect1>
4162
4163 <refsect1>
4164 <title>Arguments</title>
4165
4166 <variablelist>
4167 <varlistentry>
4168 <term><literal>Relation <parameter>rel</parameter></literal></term>
4169 <listitem>
4170 <para>
4171 input relation
4172 </para>
4173 </listitem>
4174 </varlistentry>
4175 </variablelist>
4176 </refsect1>
4177
4178 <refsect1>
4179 <title>Return Value</title>
4180
4181 <para>
4182 The name of the specified relation.
4183 </para>
4184 </refsect1>
4185 </refentry>
4186
4187 <refentry id="spi-spi-getnspname">
4188 <indexterm><primary>SPI_getnspname</primary></indexterm>
4189
4190 <refmeta>
4191 <refentrytitle>SPI_getnspname</refentrytitle>
4192 <manvolnum>3</manvolnum>
4193 </refmeta>
4194
4195 <refnamediv>
4196 <refname>SPI_getnspname</refname>
4197 <refpurpose>return the namespace of the specified relation</refpurpose>
4198 </refnamediv>
4199
4200 <refsynopsisdiv>
4201 <synopsis>
4202 char * SPI_getnspname(Relation <parameter>rel</parameter>)
4203 </synopsis>
4204 </refsynopsisdiv>
4205
4206 <refsect1>
4207 <title>Description</title>
4208
4209 <para>
4210 <function>SPI_getnspname</function> returns a copy of the name of
4211 the namespace that the specified <structname>Relation</structname>
4212 belongs to. This is equivalent to the relation's schema. You should
4213 <function>pfree</function> the return value of this function when
4214 you are finished with it.
4215 </para>
4216 </refsect1>
4217
4218 <refsect1>
4219 <title>Arguments</title>
4220
4221 <variablelist>
4222 <varlistentry>
4223 <term><literal>Relation <parameter>rel</parameter></literal></term>
4224 <listitem>
4225 <para>
4226 input relation
4227 </para>
4228 </listitem>
4229 </varlistentry>
4230 </variablelist>
4231 </refsect1>
4232
4233 <refsect1>
4234 <title>Return Value</title>
4235
4236 <para>
4237 The name of the specified relation's namespace.
4238 </para>
4239 </refsect1>
4240 </refentry>
4241
4242 <refentry id="spi-spi-result-code-string">
4243 <indexterm><primary>SPI_result_code_string</primary></indexterm>
4244
4245 <refmeta>
4246 <refentrytitle>SPI_result_code_string</refentrytitle>
4247 <manvolnum>3</manvolnum>
4248 </refmeta>
4249
4250 <refnamediv>
4251 <refname>SPI_result_code_string</refname>
4252 <refpurpose>return error code as string</refpurpose>
4253 </refnamediv>
4254
4255 <refsynopsisdiv>
4256 <synopsis>
4257 const char * SPI_result_code_string(int <parameter>code</parameter>);
4258 </synopsis>
4259 </refsynopsisdiv>
4260
4261 <refsect1>
4262 <title>Description</title>
4263
4264 <para>
4265 <function>SPI_result_code_string</function> returns a string representation
4266 of the result code returned by various SPI functions or stored
4267 in <varname>SPI_result</varname>.
4268 </para>
4269 </refsect1>
4270
4271 <refsect1>
4272 <title>Arguments</title>
4273
4274 <variablelist>
4275 <varlistentry>
4276 <term><literal>int <parameter>code</parameter></literal></term>
4277 <listitem>
4278 <para>
4279 result code
4280 </para>
4281 </listitem>
4282 </varlistentry>
4283 </variablelist>
4284 </refsect1>
4285
4286 <refsect1>
4287 <title>Return Value</title>
4288
4289 <para>
4290 A string representation of the result code.
4291 </para>
4292 </refsect1>
4293 </refentry>
4294
4295 </sect1>
4296
4297 <sect1 id="spi-memory">
4298 <title>Memory Management</title>
4299
4300 <para>
4301 <indexterm>
4302 <primary>memory context</primary>
4303 <secondary>in SPI</secondary>
4304 </indexterm>
4305 <productname>PostgreSQL</productname> allocates memory within
4306 <firstterm>memory contexts</firstterm>, which provide a convenient method of
4307 managing allocations made in many different places that need to
4308 live for differing amounts of time. Destroying a context releases
4309 all the memory that was allocated in it. Thus, it is not necessary
4310 to keep track of individual objects to avoid memory leaks; instead
4311 only a relatively small number of contexts have to be managed.
4312 <function>palloc</function> and related functions allocate memory
4313 from the <quote>current</quote> context.
4314 </para>
4315
4316 <para>
4317 <function>SPI_connect</function> creates a new memory context and
4318 makes it current. <function>SPI_finish</function> restores the
4319 previous current memory context and destroys the context created by
4320 <function>SPI_connect</function>. These actions ensure that
4321 transient memory allocations made inside your C function are
4322 reclaimed at C function exit, avoiding memory leakage.
4323 </para>
4324
4325 <para>
4326 However, if your C function needs to return an object in allocated
4327 memory (such as a value of a pass-by-reference data type), you
4328 cannot allocate that memory using <function>palloc</function>, at
4329 least not while you are connected to SPI. If you try, the object
4330 will be deallocated by <function>SPI_finish</function>, and your
4331 C function will not work reliably. To solve this problem, use
4332 <function>SPI_palloc</function> to allocate memory for your return
4333 object. <function>SPI_palloc</function> allocates memory in the
4334 <quote>upper executor context</quote>, that is, the memory context
4335 that was current when <function>SPI_connect</function> was called,
4336 which is precisely the right context for a value returned from your
4337 C function. Several of the other utility functions described in
4338 this section also return objects created in the upper executor context.
4339 </para>
4340
4341 <para>
4342 When <function>SPI_connect</function> is called, the private
4343 context of the C function, which is created by
4344 <function>SPI_connect</function>, is made the current context. All
4345 allocations made by <function>palloc</function>,
4346 <function>repalloc</function>, or SPI utility functions (except as
4347 described in this section) are made in this context. When a
4348 C function disconnects from the SPI manager (via
4349 <function>SPI_finish</function>) the current context is restored to
4350 the upper executor context, and all allocations made in the
4351 C function memory context are freed and cannot be used any more.
4352 </para>
4353
4354 <!-- *********************************************** -->
4355
4356 <refentry id="spi-spi-palloc">
4357 <indexterm><primary>SPI_palloc</primary></indexterm>
4358
4359 <refmeta>
4360 <refentrytitle>SPI_palloc</refentrytitle>
4361 <manvolnum>3</manvolnum>
4362 </refmeta>
4363
4364 <refnamediv>
4365 <refname>SPI_palloc</refname>
4366 <refpurpose>allocate memory in the upper executor context</refpurpose>
4367 </refnamediv>
4368
4369 <refsynopsisdiv>
4370 <synopsis>
4371 void * SPI_palloc(Size <parameter>size</parameter>)
4372 </synopsis>
4373 </refsynopsisdiv>
4374
4375 <refsect1>
4376 <title>Description</title>
4377
4378 <para>
4379 <function>SPI_palloc</function> allocates memory in the upper
4380 executor context.
4381 </para>
4382
4383 <para>
4384 This function can only be used while connected to SPI.
4385 Otherwise, it throws an error.
4386 </para>
4387 </refsect1>
4388
4389 <refsect1>
4390 <title>Arguments</title>
4391
4392 <variablelist>
4393 <varlistentry>
4394 <term><literal>Size <parameter>size</parameter></literal></term>
4395 <listitem>
4396 <para>
4397 size in bytes of storage to allocate
4398 </para>
4399 </listitem>
4400 </varlistentry>
4401 </variablelist>
4402 </refsect1>
4403
4404 <refsect1>
4405 <title>Return Value</title>
4406
4407 <para>
4408 pointer to new storage space of the specified size
4409 </para>
4410 </refsect1>
4411 </refentry>
4412
4413 <!-- *********************************************** -->
4414
4415 <refentry id="spi-realloc">
4416 <indexterm><primary>SPI_repalloc</primary></indexterm>
4417
4418 <refmeta>
4419 <refentrytitle>SPI_repalloc</refentrytitle>
4420 <manvolnum>3</manvolnum>
4421 </refmeta>
4422
4423 <refnamediv>
4424 <refname>SPI_repalloc</refname>
4425 <refpurpose>reallocate memory in the upper executor context</refpurpose>
4426 </refnamediv>
4427
4428 <refsynopsisdiv>
4429 <synopsis>
4430 void * SPI_repalloc(void * <parameter>pointer</parameter>, Size <parameter>size</parameter>)
4431 </synopsis>
4432 </refsynopsisdiv>
4433
4434 <refsect1>
4435 <title>Description</title>
4436
4437 <para>
4438 <function>SPI_repalloc</function> changes the size of a memory
4439 segment previously allocated using <function>SPI_palloc</function>.
4440 </para>
4441
4442 <para>
4443 This function is no longer different from plain
4444 <function>repalloc</function>. It's kept just for backward
4445 compatibility of existing code.
4446 </para>
4447 </refsect1>
4448
4449 <refsect1>
4450 <title>Arguments</title>
4451
4452 <variablelist>
4453 <varlistentry>
4454 <term><literal>void * <parameter>pointer</parameter></literal></term>
4455 <listitem>
4456 <para>
4457 pointer to existing storage to change
4458 </para>
4459 </listitem>
4460 </varlistentry>
4461
4462 <varlistentry>
4463 <term><literal>Size <parameter>size</parameter></literal></term>
4464 <listitem>
4465 <para>
4466 size in bytes of storage to allocate
4467 </para>
4468 </listitem>
4469 </varlistentry>
4470 </variablelist>
4471 </refsect1>
4472
4473 <refsect1>
4474 <title>Return Value</title>
4475
4476 <para>
4477 pointer to new storage space of specified size with the contents
4478 copied from the existing area
4479 </para>
4480 </refsect1>
4481 </refentry>
4482
4483 <!-- *********************************************** -->
4484
4485 <refentry id="spi-spi-pfree">
4486 <indexterm><primary>SPI_pfree</primary></indexterm>
4487
4488 <refmeta>
4489 <refentrytitle>SPI_pfree</refentrytitle>
4490 <manvolnum>3</manvolnum>
4491 </refmeta>
4492
4493 <refnamediv>
4494 <refname>SPI_pfree</refname>
4495 <refpurpose>free memory in the upper executor context</refpurpose>
4496 </refnamediv>
4497
4498 <refsynopsisdiv>
4499 <synopsis>
4500 void SPI_pfree(void * <parameter>pointer</parameter>)
4501 </synopsis>
4502 </refsynopsisdiv>
4503
4504 <refsect1>
4505 <title>Description</title>
4506
4507 <para>
4508 <function>SPI_pfree</function> frees memory previously allocated
4509 using <function>SPI_palloc</function> or
4510 <function>SPI_repalloc</function>.
4511 </para>
4512
4513 <para>
4514 This function is no longer different from plain
4515 <function>pfree</function>. It's kept just for backward
4516 compatibility of existing code.
4517 </para>
4518 </refsect1>
4519
4520 <refsect1>
4521 <title>Arguments</title>
4522
4523 <variablelist>
4524 <varlistentry>
4525 <term><literal>void * <parameter>pointer</parameter></literal></term>
4526 <listitem>
4527 <para>
4528 pointer to existing storage to free
4529 </para>
4530 </listitem>
4531 </varlistentry>
4532 </variablelist>
4533 </refsect1>
4534 </refentry>
4535
4536 <!-- *********************************************** -->
4537
4538 <refentry id="spi-spi-copytuple">
4539 <indexterm><primary>SPI_copytuple</primary></indexterm>
4540
4541 <refmeta>
4542 <refentrytitle>SPI_copytuple</refentrytitle>
4543 <manvolnum>3</manvolnum>
4544 </refmeta>
4545
4546 <refnamediv>
4547 <refname>SPI_copytuple</refname>
4548 <refpurpose>make a copy of a row in the upper executor context</refpurpose>
4549 </refnamediv>
4550
4551 <refsynopsisdiv>
4552 <synopsis>
4553 HeapTuple SPI_copytuple(HeapTuple <parameter>row</parameter>)
4554 </synopsis>
4555 </refsynopsisdiv>
4556
4557 <refsect1>
4558 <title>Description</title>
4559
4560 <para>
4561 <function>SPI_copytuple</function> makes a copy of a row in the
4562 upper executor context. This is normally used to return a modified
4563 row from a trigger. In a function declared to return a composite
4564 type, use <function>SPI_returntuple</function> instead.
4565 </para>
4566
4567 <para>
4568 This function can only be used while connected to SPI.
4569 Otherwise, it returns NULL and sets <varname>SPI_result</varname> to
4570 <symbol>SPI_ERROR_UNCONNECTED</symbol>.
4571 </para>
4572 </refsect1>
4573
4574 <refsect1>
4575 <title>Arguments</title>
4576
4577 <variablelist>
4578 <varlistentry>
4579 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
4580 <listitem>
4581 <para>
4582 row to be copied
4583 </para>
4584 </listitem>
4585 </varlistentry>
4586 </variablelist>
4587 </refsect1>
4588
4589 <refsect1>
4590 <title>Return Value</title>
4591
4592 <para>
4593 the copied row, or <symbol>NULL</symbol> on error
4594 (see <varname>SPI_result</varname> for an error indication)
4595 </para>
4596 </refsect1>
4597 </refentry>
4598
4599 <!-- *********************************************** -->
4600
4601 <refentry id="spi-spi-returntuple">
4602 <indexterm><primary>SPI_returntuple</primary></indexterm>
4603
4604 <refmeta>
4605 <refentrytitle>SPI_returntuple</refentrytitle>
4606 <manvolnum>3</manvolnum>
4607 </refmeta>
4608
4609 <refnamediv>
4610 <refname>SPI_returntuple</refname>
4611 <refpurpose>prepare to return a tuple as a Datum</refpurpose>
4612 </refnamediv>
4613
4614 <refsynopsisdiv>
4615 <synopsis>
4616 HeapTupleHeader SPI_returntuple(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>)
4617 </synopsis>
4618 </refsynopsisdiv>
4619
4620 <refsect1>
4621 <title>Description</title>
4622
4623 <para>
4624 <function>SPI_returntuple</function> makes a copy of a row in
4625 the upper executor context, returning it in the form of a row type <type>Datum</type>.
4626 The returned pointer need only be converted to <type>Datum</type> via <function>PointerGetDatum</function>
4627 before returning.
4628 </para>
4629
4630 <para>
4631 This function can only be used while connected to SPI.
4632 Otherwise, it returns NULL and sets <varname>SPI_result</varname> to
4633 <symbol>SPI_ERROR_UNCONNECTED</symbol>.
4634 </para>
4635
4636 <para>
4637 Note that this should be used for functions that are declared to return
4638 composite types. It is not used for triggers; use
4639 <function>SPI_copytuple</function> for returning a modified row in a trigger.
4640 </para>
4641 </refsect1>
4642
4643 <refsect1>
4644 <title>Arguments</title>
4645
4646 <variablelist>
4647 <varlistentry>
4648 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
4649 <listitem>
4650 <para>
4651 row to be copied
4652 </para>
4653 </listitem>
4654 </varlistentry>
4655
4656 <varlistentry>
4657 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
4658 <listitem>
4659 <para>
4660 descriptor for row (pass the same descriptor each time for most
4661 effective caching)
4662 </para>
4663 </listitem>
4664 </varlistentry>
4665 </variablelist>
4666 </refsect1>
4667
4668 <refsect1>
4669 <title>Return Value</title>
4670
4671 <para>
4672 <type>HeapTupleHeader</type> pointing to copied row,
4673 or <symbol>NULL</symbol> on error
4674 (see <varname>SPI_result</varname> for an error indication)
4675 </para>
4676 </refsect1>
4677 </refentry>
4678
4679 <!-- *********************************************** -->
4680
4681 <refentry id="spi-spi-modifytuple">
4682 <indexterm><primary>SPI_modifytuple</primary></indexterm>
4683
4684 <refmeta>
4685 <refentrytitle>SPI_modifytuple</refentrytitle>
4686 <manvolnum>3</manvolnum>
4687 </refmeta>
4688
4689 <refnamediv>
4690 <refname>SPI_modifytuple</refname>
4691 <refpurpose>create a row by replacing selected fields of a given row</refpurpose>
4692 </refnamediv>
4693
4694 <refsynopsisdiv>
4695 <synopsis>
4696 HeapTuple SPI_modifytuple(Relation <parameter>rel</parameter>, HeapTuple <parameter>row</parameter>, int <parameter>ncols</parameter>,
4697 int * <parameter>colnum</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>)
4698 </synopsis>
4699 </refsynopsisdiv>
4700
4701 <refsect1>
4702 <title>Description</title>
4703
4704 <para>
4705 <function>SPI_modifytuple</function> creates a new row by
4706 substituting new values for selected columns, copying the original
4707 row's columns at other positions. The input row is not modified.
4708 The new row is returned in the upper executor context.
4709 </para>
4710
4711 <para>
4712 This function can only be used while connected to SPI.
4713 Otherwise, it returns NULL and sets <varname>SPI_result</varname> to
4714 <symbol>SPI_ERROR_UNCONNECTED</symbol>.
4715 </para>
4716 </refsect1>
4717
4718 <refsect1>
4719 <title>Arguments</title>
4720
4721 <variablelist>
4722 <varlistentry>
4723 <term><literal>Relation <parameter>rel</parameter></literal></term>
4724 <listitem>
4725 <para>
4726 Used only as the source of the row descriptor for the row.
4727 (Passing a relation rather than a row descriptor is a
4728 misfeature.)
4729 </para>
4730 </listitem>
4731 </varlistentry>
4732
4733 <varlistentry>
4734 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
4735 <listitem>
4736 <para>
4737 row to be modified
4738 </para>
4739 </listitem>
4740 </varlistentry>
4741
4742 <varlistentry>
4743 <term><literal>int <parameter>ncols</parameter></literal></term>
4744 <listitem>
4745 <para>
4746 number of columns to be changed
4747 </para>
4748 </listitem>
4749 </varlistentry>
4750
4751 <varlistentry>
4752 <term><literal>int * <parameter>colnum</parameter></literal></term>
4753 <listitem>
4754 <para>
4755 an array of length <parameter>ncols</parameter>, containing the numbers
4756 of the columns that are to be changed (column numbers start at 1)
4757 </para>
4758 </listitem>
4759 </varlistentry>
4760
4761 <varlistentry>
4762 <term><literal>Datum * <parameter>values</parameter></literal></term>
4763 <listitem>
4764 <para>
4765 an array of length <parameter>ncols</parameter>, containing the
4766 new values for the specified columns
4767 </para>
4768 </listitem>
4769 </varlistentry>
4770
4771 <varlistentry>
4772 <term><literal>const char * <parameter>nulls</parameter></literal></term>
4773 <listitem>
4774 <para>
4775 an array of length <parameter>ncols</parameter>, describing which
4776 new values are null
4777 </para>
4778
4779 <para>
4780 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
4781 <function>SPI_modifytuple</function> assumes that no new values
4782 are null. Otherwise, each entry of the <parameter>nulls</parameter>
4783 array should be <literal>'&nbsp;'</literal> if the corresponding new value is
4784 non-null, or <literal>'n'</literal> if the corresponding new value is
4785 null. (In the latter case, the actual value in the corresponding
4786 <parameter>values</parameter> entry doesn't matter.) Note that
4787 <parameter>nulls</parameter> is not a text string, just an array: it
4788 does not need a <literal>'\0'</literal> terminator.
4789 </para>
4790 </listitem>
4791 </varlistentry>
4792 </variablelist>
4793 </refsect1>
4794
4795 <refsect1>
4796 <title>Return Value</title>
4797
4798 <para>
4799 new row with modifications, allocated in the upper executor
4800 context, or <symbol>NULL</symbol> on error
4801 (see <varname>SPI_result</varname> for an error indication)
4802 </para>
4803
4804 <para>
4805 On error, <varname>SPI_result</varname> is set as follows:
4806 <variablelist>
4807 <varlistentry>
4808 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
4809 <listitem>
4810 <para>
4811 if <parameter>rel</parameter> is <symbol>NULL</symbol>, or if
4812 <parameter>row</parameter> is <symbol>NULL</symbol>, or if <parameter>ncols</parameter>
4813 is less than or equal to 0, or if <parameter>colnum</parameter> is
4814 <symbol>NULL</symbol>, or if <parameter>values</parameter> is <symbol>NULL</symbol>.
4815 </para>
4816 </listitem>
4817 </varlistentry>
4818
4819 <varlistentry>
4820 <term><symbol>SPI_ERROR_NOATTRIBUTE</symbol></term>
4821 <listitem>
4822 <para>
4823 if <parameter>colnum</parameter> contains an invalid column number (less
4824 than or equal to 0 or greater than the number of columns in
4825 <parameter>row</parameter>)
4826 </para>
4827 </listitem>
4828 </varlistentry>
4829
4830 <varlistentry>
4831 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
4832 <listitem>
4833 <para>
4834 if SPI is not active
4835 </para>
4836 </listitem>
4837 </varlistentry>
4838 </variablelist>
4839 </para>
4840 </refsect1>
4841 </refentry>
4842
4843 <!-- *********************************************** -->
4844
4845 <refentry id="spi-spi-freetuple">
4846 <indexterm><primary>SPI_freetuple</primary></indexterm>
4847
4848 <refmeta>
4849 <refentrytitle>SPI_freetuple</refentrytitle>
4850 <manvolnum>3</manvolnum>
4851 </refmeta>
4852
4853 <refnamediv>
4854 <refname>SPI_freetuple</refname>
4855 <refpurpose>free a row allocated in the upper executor context</refpurpose>
4856 </refnamediv>
4857
4858 <refsynopsisdiv>
4859 <synopsis>
4860 void SPI_freetuple(HeapTuple <parameter>row</parameter>)
4861 </synopsis>
4862 </refsynopsisdiv>
4863
4864 <refsect1>
4865 <title>Description</title>
4866
4867 <para>
4868 <function>SPI_freetuple</function> frees a row previously allocated
4869 in the upper executor context.
4870 </para>
4871
4872 <para>
4873 This function is no longer different from plain
4874 <function>heap_freetuple</function>. It's kept just for backward
4875 compatibility of existing code.
4876 </para>
4877 </refsect1>
4878
4879 <refsect1>
4880 <title>Arguments</title>
4881
4882 <variablelist>
4883 <varlistentry>
4884 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
4885 <listitem>
4886 <para>
4887 row to free
4888 </para>
4889 </listitem>
4890 </varlistentry>
4891 </variablelist>
4892 </refsect1>
4893 </refentry>
4894
4895 <!-- *********************************************** -->
4896
4897 <refentry id="spi-spi-freetupletable">
4898 <indexterm><primary>SPI_freetuptable</primary></indexterm>
4899
4900 <refmeta>
4901 <refentrytitle>SPI_freetuptable</refentrytitle>
4902 <manvolnum>3</manvolnum>
4903 </refmeta>
4904
4905 <refnamediv>
4906 <refname>SPI_freetuptable</refname>
4907 <refpurpose>free a row set created by <function>SPI_execute</function> or a similar
4908 function</refpurpose>
4909 </refnamediv>
4910
4911 <refsynopsisdiv>
4912 <synopsis>
4913 void SPI_freetuptable(SPITupleTable * <parameter>tuptable</parameter>)
4914 </synopsis>
4915 </refsynopsisdiv>
4916
4917 <refsect1>
4918 <title>Description</title>
4919
4920 <para>
4921 <function>SPI_freetuptable</function> frees a row set created by a
4922 prior SPI command execution function, such as
4923 <function>SPI_execute</function>. Therefore, this function is often called
4924 with the global variable <varname>SPI_tuptable</varname> as
4925 argument.
4926 </para>
4927
4928 <para>
4929 This function is useful if an SPI-using C function needs to execute
4930 multiple commands and does not want to keep the results of earlier
4931 commands around until it ends. Note that any unfreed row sets will
4932 be freed anyway at <function>SPI_finish</function>.
4933 Also, if a subtransaction is started and then aborted within execution
4934 of an SPI-using C function, SPI automatically frees any row sets created while
4935 the subtransaction was running.
4936 </para>
4937
4938 <para>
4939 Beginning in <productname>PostgreSQL</productname> 9.3,
4940 <function>SPI_freetuptable</function> contains guard logic to protect
4941 against duplicate deletion requests for the same row set. In previous
4942 releases, duplicate deletions would lead to crashes.
4943 </para>
4944 </refsect1>
4945
4946 <refsect1>
4947 <title>Arguments</title>
4948
4949 <variablelist>
4950 <varlistentry>
4951 <term><literal>SPITupleTable * <parameter>tuptable</parameter></literal></term>
4952 <listitem>
4953 <para>
4954 pointer to row set to free, or NULL to do nothing
4955 </para>
4956 </listitem>
4957 </varlistentry>
4958 </variablelist>
4959 </refsect1>
4960 </refentry>
4961
4962 <!-- *********************************************** -->
4963
4964 <refentry id="spi-spi-freeplan">
4965 <indexterm><primary>SPI_freeplan</primary></indexterm>
4966
4967 <refmeta>
4968 <refentrytitle>SPI_freeplan</refentrytitle>
4969 <manvolnum>3</manvolnum>
4970 </refmeta>
4971
4972 <refnamediv>
4973 <refname>SPI_freeplan</refname>
4974 <refpurpose>free a previously saved prepared statement</refpurpose>
4975 </refnamediv>
4976
4977 <refsynopsisdiv>
4978 <synopsis>
4979 int SPI_freeplan(SPIPlanPtr <parameter>plan</parameter>)
4980 </synopsis>
4981 </refsynopsisdiv>
4982
4983 <refsect1>
4984 <title>Description</title>
4985
4986 <para>
4987 <function>SPI_freeplan</function> releases a prepared statement
4988 previously returned by <function>SPI_prepare</function> or saved by
4989 <function>SPI_keepplan</function> or <function>SPI_saveplan</function>.
4990 </para>
4991 </refsect1>
4992
4993 <refsect1>
4994 <title>Arguments</title>
4995
4996 <variablelist>
4997 <varlistentry>
4998 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
4999 <listitem>
5000 <para>
5001 pointer to statement to free
5002 </para>
5003 </listitem>
5004 </varlistentry>
5005 </variablelist>
5006 </refsect1>
5007
5008 <refsect1>
5009 <title>Return Value</title>
5010
5011 <para>
5012 0 on success;
5013 <symbol>SPI_ERROR_ARGUMENT</symbol> if <parameter>plan</parameter>
5014 is <symbol>NULL</symbol> or invalid
5015 </para>
5016 </refsect1>
5017 </refentry>
5018
5019 </sect1>
5020
5021 <sect1 id="spi-transaction">
5022 <title>Transaction Management</title>
5023
5024 <para>
5025 It is not possible to run transaction control commands such
5026 as <command>COMMIT</command> and <command>ROLLBACK</command> through SPI
5027 functions such as <function>SPI_execute</function>. There are, however,
5028 separate interface functions that allow transaction control through SPI.
5029 </para>
5030
5031 <para>
5032 It is not generally safe and sensible to start and end transactions in
5033 arbitrary user-defined SQL-callable functions without taking into account
5034 the context in which they are called. For example, a transaction boundary
5035 in the middle of a function that is part of a complex SQL expression that
5036 is part of some SQL command will probably result in obscure internal errors
5037 or crashes. The interface functions presented here are primarily intended
5038 to be used by procedural language implementations to support transaction
5039 management in SQL-level procedures that are invoked by the <command>CALL</command>
5040 command, taking the context of the <command>CALL</command> invocation into
5041 account. SPI-using procedures implemented in C can implement the same logic, but
5042 the details of that are beyond the scope of this documentation.
5043 </para>
5044
5045 <!-- *********************************************** -->
5046
5047 <refentry id="spi-spi-commit">
5048 <indexterm><primary>SPI_commit</primary></indexterm>
5049 <indexterm><primary>SPI_commit_and_chain</primary></indexterm>
5050
5051 <refmeta>
5052 <refentrytitle>SPI_commit</refentrytitle>
5053 <manvolnum>3</manvolnum>
5054 </refmeta>
5055
5056 <refnamediv>
5057 <refname>SPI_commit</refname>
5058 <refname>SPI_commit_and_chain</refname>
5059 <refpurpose>commit the current transaction</refpurpose>
5060 </refnamediv>
5061
5062 <refsynopsisdiv>
5063 <synopsis>
5064 void SPI_commit(void)
5065 </synopsis>
5066
5067 <synopsis>
5068 void SPI_commit_and_chain(void)
5069 </synopsis>
5070 </refsynopsisdiv>
5071
5072 <refsect1>
5073 <title>Description</title>
5074
5075 <para>
5076 <function>SPI_commit</function> commits the current transaction. It is
5077 approximately equivalent to running the SQL
5078 command <command>COMMIT</command>. After the transaction is committed, a
5079 new transaction is automatically started using default transaction
5080 characteristics, so that the caller can continue using SPI facilities.
5081 If there is a failure during commit, the current transaction is instead
5082 rolled back and a new transaction is started, after which the error is
5083 thrown in the usual way.
5084 </para>
5085
5086 <para>
5087 <function>SPI_commit_and_chain</function> is the same, but the new
5088 transaction is started with the same transaction
5089 characteristics as the just finished one, like with the SQL command
5090 <command>COMMIT AND CHAIN</command>.
5091 </para>
5092
5093 <para>
5094 These functions can only be executed if the SPI connection has been set as
5095 nonatomic in the call to <function>SPI_connect_ext</function>.
5096 </para>
5097 </refsect1>
5098 </refentry>
5099
5100 <!-- *********************************************** -->
5101
5102 <refentry id="spi-spi-rollback">
5103 <indexterm><primary>SPI_rollback</primary></indexterm>
5104 <indexterm><primary>SPI_rollback_and_chain</primary></indexterm>
5105
5106 <refmeta>
5107 <refentrytitle>SPI_rollback</refentrytitle>
5108 <manvolnum>3</manvolnum>
5109 </refmeta>
5110
5111 <refnamediv>
5112 <refname>SPI_rollback</refname>
5113 <refname>SPI_rollback_and_chain</refname>
5114 <refpurpose>abort the current transaction</refpurpose>
5115 </refnamediv>
5116
5117 <refsynopsisdiv>
5118 <synopsis>
5119 void SPI_rollback(void)
5120 </synopsis>
5121
5122 <synopsis>
5123 void SPI_rollback_and_chain(void)
5124 </synopsis>
5125 </refsynopsisdiv>
5126
5127 <refsect1>
5128 <title>Description</title>
5129
5130 <para>
5131 <function>SPI_rollback</function> rolls back the current transaction. It
5132 is approximately equivalent to running the SQL
5133 command <command>ROLLBACK</command>. After the transaction is rolled back,
5134 a new transaction is automatically started using default transaction
5135 characteristics, so that the caller can continue using SPI facilities.
5136 </para>
5137 <para>
5138 <function>SPI_rollback_and_chain</function> is the same, but the new
5139 transaction is started with the same transaction
5140 characteristics as the just finished one, like with the SQL command
5141 <command>ROLLBACK AND CHAIN</command>.
5142 </para>
5143
5144 <para>
5145 These functions can only be executed if the SPI connection has been set as
5146 nonatomic in the call to <function>SPI_connect_ext</function>.
5147 </para>
5148 </refsect1>
5149 </refentry>
5150
5151 <!-- *********************************************** -->
5152
5153 <refentry id="spi-spi-start-transaction">
5154 <indexterm><primary>SPI_start_transaction</primary></indexterm>
5155
5156 <refmeta>
5157 <refentrytitle>SPI_start_transaction</refentrytitle>
5158 <manvolnum>3</manvolnum>
5159 </refmeta>
5160
5161 <refnamediv>
5162 <refname>SPI_start_transaction</refname>
5163 <refpurpose>obsolete function</refpurpose>
5164 </refnamediv>
5165
5166 <refsynopsisdiv>
5167 <synopsis>
5168 void SPI_start_transaction(void)
5169 </synopsis>
5170 </refsynopsisdiv>
5171
5172 <refsect1>
5173 <title>Description</title>
5174
5175 <para>
5176 <function>SPI_start_transaction</function> does nothing, and exists
5177 only for code compatibility with
5178 earlier <productname>PostgreSQL</productname> releases. It used to
5179 be required after calling <function>SPI_commit</function>
5180 or <function>SPI_rollback</function>, but now those functions start
5181 a new transaction automatically.
5182 </para>
5183 </refsect1>
5184 </refentry>
5185
5186 </sect1>
5187
5188 <sect1 id="spi-visibility">
5189 <title>Visibility of Data Changes</title>
5190
5191 <para>
5192 The following rules govern the visibility of data changes in
5193 functions that use SPI (or any other C function):
5194
5195 <itemizedlist>
5196 <listitem>
5197 <para>
5198 During the execution of an SQL command, any data changes made by
5199 the command are invisible to the command itself. For
5200 example, in:
5201 <programlisting>
5202 INSERT INTO a SELECT * FROM a;
5203 </programlisting>
5204 the inserted rows are invisible to the <command>SELECT</command>
5205 part.
5206 </para>
5207 </listitem>
5208
5209 <listitem>
5210 <para>
5211 Changes made by a command C are visible to all commands that are
5212 started after C, no matter whether they are started inside C
5213 (during the execution of C) or after C is done.
5214 </para>
5215 </listitem>
5216
5217 <listitem>
5218 <para>
5219 Commands executed via SPI inside a function called by an SQL command
5220 (either an ordinary function or a trigger) follow one or the
5221 other of the above rules depending on the read/write flag passed
5222 to SPI. Commands executed in read-only mode follow the first
5223 rule: they cannot see changes of the calling command. Commands executed
5224 in read-write mode follow the second rule: they can see all changes made
5225 so far.
5226 </para>
5227 </listitem>
5228
5229 <listitem>
5230 <para>
5231 All standard procedural languages set the SPI read-write mode
5232 depending on the volatility attribute of the function. Commands of
5233 <literal>STABLE</literal> and <literal>IMMUTABLE</literal> functions are done in
5234 read-only mode, while commands of <literal>VOLATILE</literal> functions are
5235 done in read-write mode. While authors of C functions are able to
5236 violate this convention, it's unlikely to be a good idea to do so.
5237 </para>
5238 </listitem>
5239 </itemizedlist>
5240 </para>
5241
5242 <para>
5243 The next section contains an example that illustrates the
5244 application of these rules.
5245 </para>
5246 </sect1>
5247
5248 <sect1 id="spi-examples">
5249 <title>Examples</title>
5250
5251 <para>
5252 This section contains a very simple example of SPI usage. The
5253 C function <function>execq</function> takes an SQL command as its
5254 first argument and a row count as its second, executes the command
5255 using <function>SPI_exec</function> and returns the number of rows
5256 that were processed by the command. You can find more complex
5257 examples for SPI in the source tree in
5258 <filename>src/test/regress/regress.c</filename> and in the
5259 <xref linkend="contrib-spi"/> module.
5260 </para>
5261
5262 <programlisting>
5263 #include "postgres.h"
5264
5265 #include "executor/spi.h"
5266 #include "utils/builtins.h"
5267
5268 PG_MODULE_MAGIC;
5269
5270 PG_FUNCTION_INFO_V1(execq);
5271
5272 Datum
5273 execq(PG_FUNCTION_ARGS)
5274 {
5275 char *command;
5276 int cnt;
5277 int ret;
5278 uint64 proc;
5279
5280 /* Convert given text object to a C string */
5281 command = text_to_cstring(PG_GETARG_TEXT_PP(0));
5282 cnt = PG_GETARG_INT32(1);
5283
5284 SPI_connect();
5285
5286 ret = SPI_exec(command, cnt);
5287
5288 proc = SPI_processed;
5289
5290 /*
5291 * If some rows were fetched, print them via elog(INFO).
5292 */
5293 if (ret &gt; 0 &amp;&amp; SPI_tuptable != NULL)
5294 {
5295 SPITupleTable *tuptable = SPI_tuptable;
5296 TupleDesc tupdesc = tuptable-&gt;tupdesc;
5297 char buf[8192];
5298 uint64 j;
5299
5300 for (j = 0; j &lt; tuptable-&gt;numvals; j++)
5301 {
5302 HeapTuple tuple = tuptable-&gt;vals[j];
5303 int i;
5304
5305 for (i = 1, buf[0] = 0; i &lt;= tupdesc-&gt;natts; i++)
5306 snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), " %s%s",
5307 SPI_getvalue(tuple, tupdesc, i),
5308 (i == tupdesc-&gt;natts) ? " " : " |");
5309 elog(INFO, "EXECQ: %s", buf);
5310 }
5311 }
5312
5313 SPI_finish();
5314 pfree(command);
5315
5316 PG_RETURN_INT64(proc);
5317 }
5318 </programlisting>
5319
5320 <para>
5321 This is how you declare the function after having compiled it into
5322 a shared library (details are in <xref linkend="dfunc"/>.):
5323
5324 <programlisting>
5325 CREATE FUNCTION execq(text, integer) RETURNS int8
5326 AS '<replaceable>filename</replaceable>'
5327 LANGUAGE C STRICT;
5328 </programlisting>
5329 </para>
5330
5331 <para>
5332 Here is a sample session:
5333
5334 <programlisting>
5335 =&gt; SELECT execq('CREATE TABLE a (x integer)', 0);
5336 execq
5337 -------
5338 0
5339 (1 row)
5340
5341 =&gt; INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
5342 INSERT 0 1
5343 =&gt; SELECT execq('SELECT * FROM a', 0);
5344 INFO: EXECQ: 0 <lineannotation>-- inserted by execq</lineannotation>
5345 INFO: EXECQ: 1 <lineannotation>-- returned by execq and inserted by upper INSERT</lineannotation>
5346
5347 execq
5348 -------
5349 2
5350 (1 row)
5351
5352 =&gt; SELECT execq('INSERT INTO a SELECT x + 2 FROM a RETURNING *', 1);
5353 INFO: EXECQ: 2 <lineannotation>-- 0 + 2, then execution was stopped by count</lineannotation>
5354 execq
5355 -------
5356 1
5357 (1 row)
5358
5359 =&gt; SELECT execq('SELECT * FROM a', 10);
5360 INFO: EXECQ: 0
5361 INFO: EXECQ: 1
5362 INFO: EXECQ: 2
5363
5364 execq
5365 -------
5366 3 <lineannotation>-- 10 is the max value only, 3 is the real number of rows</lineannotation>
5367 (1 row)
5368
5369 =&gt; SELECT execq('INSERT INTO a SELECT x + 10 FROM a', 1);
5370 execq
5371 -------
5372 3 <lineannotation>-- all rows processed; count does not stop it, because nothing is returned</lineannotation>
5373 (1 row)
5374
5375 =&gt; SELECT * FROM a;
5376 x
5377 ----
5378 0
5379 1
5380 2
5381 10
5382 11
5383 12
5384 (6 rows)
5385
5386 =&gt; DELETE FROM a;
5387 DELETE 6
5388 =&gt; INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
5389 INSERT 0 1
5390 =&gt; SELECT * FROM a;
5391 x
5392 ---
5393 1 <lineannotation>-- 0 (no rows in a) + 1</lineannotation>
5394 (1 row)
5395
5396 =&gt; INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
5397 INFO: EXECQ: 1
5398 INSERT 0 1
5399 =&gt; SELECT * FROM a;
5400 x
5401 ---
5402 1
5403 2 <lineannotation>-- 1 (there was one row in a) + 1</lineannotation>
5404 (2 rows)
5405
5406 <lineannotation>-- This demonstrates the data changes visibility rule.</lineannotation>
5407 <lineannotation>-- execq is called twice and sees different numbers of rows each time:</lineannotation>
5408
5409 =&gt; INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;
5410 INFO: EXECQ: 1 <lineannotation>-- results from first execq</lineannotation>
5411 INFO: EXECQ: 2
5412 INFO: EXECQ: 1 <lineannotation>-- results from second execq</lineannotation>
5413 INFO: EXECQ: 2
5414 INFO: EXECQ: 2
5415 INSERT 0 2
5416 =&gt; SELECT * FROM a;
5417 x
5418 ---
5419 1
5420 2
5421 2 <lineannotation>-- 2 rows * 1 (x in first row)</lineannotation>
5422 6 <lineannotation>-- 3 rows (2 + 1 just inserted) * 2 (x in second row)</lineannotation>
5423 (4 rows)
5424 </programlisting>
5425 </para>
5426 </sect1>
5427 </chapter>