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