Skip to content

Commit ea2e263

Browse files
tglsfdctglsfdc
committed
Add new return codes SPI_OK_INSERT_RETURNING etc to the SPI API.
Fix all the standard PLs to be able to return tuples from FOO_RETURNING statements as well as utility statements that return tuples. Also, fix oversight that SPI_processed wasn't set for a utility statement returning tuples. Per recent discussion.
1 parent 7a2fe85 commit ea2e263

File tree

7 files changed

+97
-56
lines changed

7 files changed

+97
-56
lines changed

‎doc/src/sgml/spi.sgml

Lines changed: 44 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.46 2006/08/12 20:05:54 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.47 2006/08/27 23:47:57 tgl Exp $ -->
22

33
<chapter id="spi">
44
<title>Server Programming Interface</title>
@@ -361,12 +361,16 @@ SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
361361

362362
<para>
363363
The actual number of rows for which the (last) command was executed
364-
is returned in the global variable <varname>SPI_processed</varname>
365-
(unless the return value of the function is
366-
<symbol>SPI_OK_UTILITY</symbol>). If the return value of the
367-
function is <symbol>SPI_OK_SELECT</symbol> then you may use the
364+
is returned in the global variable <varname>SPI_processed</varname>.
365+
If the return value of the function is <symbol>SPI_OK_SELECT</symbol>,
366+
<symbol>SPI_OK_INSERT_RETURNING</symbol>,
367+
<symbol>SPI_OK_DELETE_RETURNING</symbol>, or
368+
<symbol>SPI_OK_UPDATE_RETURNING</symbol>,
369+
then you may use the
368370
global pointer <literal>SPITupleTable *SPI_tuptable</literal> to
369-
access the result rows.
371+
access the result rows. Some utility commands (such as
372+
<command>EXPLAIN</>) also return rowsets, and <literal>SPI_tuptable</>
373+
will contain the result in these cases too.
370374
</para>
371375

372376
<para>
@@ -459,19 +463,19 @@ typedef struct
459463
</varlistentry>
460464

461465
<varlistentry>
462-
<term><symbol>SPI_OK_DELETE</symbol></term>
466+
<term><symbol>SPI_OK_INSERT</symbol></term>
463467
<listitem>
464468
<para>
465-
if a <command>DELETE</command> was executed
469+
if an <command>INSERT</command> was executed
466470
</para>
467471
</listitem>
468472
</varlistentry>
469473

470474
<varlistentry>
471-
<term><symbol>SPI_OK_INSERT</symbol></term>
475+
<term><symbol>SPI_OK_DELETE</symbol></term>
472476
<listitem>
473477
<para>
474-
if an <command>INSERT</command> was executed
478+
if a <command>DELETE</command> was executed
475479
</para>
476480
</listitem>
477481
</varlistentry>
@@ -485,6 +489,33 @@ typedef struct
485489
</listitem>
486490
</varlistentry>
487491

492+
<varlistentry>
493+
<term><symbol>SPI_OK_INSERT_RETURNING</symbol></term>
494+
<listitem>
495+
<para>
496+
if an <command>INSERT RETURNING</command> was executed
497+
</para>
498+
</listitem>
499+
</varlistentry>
500+
501+
<varlistentry>
502+
<term><symbol>SPI_OK_DELETE_RETURNING</symbol></term>
503+
<listitem>
504+
<para>
505+
if a <command>DELETE RETURNING</command> was executed
506+
</para>
507+
</listitem>
508+
</varlistentry>
509+
510+
<varlistentry>
511+
<term><symbol>SPI_OK_UPDATE_RETURNING</symbol></term>
512+
<listitem>
513+
<para>
514+
if an <command>UPDATE RETURNING</command> was executed
515+
</para>
516+
</listitem>
517+
</varlistentry>
518+
488519
<varlistentry>
489520
<term><symbol>SPI_OK_UTILITY</symbol></term>
490521
<listitem>
@@ -2987,10 +3018,9 @@ execq(text *sql, int cnt)
29873018

29883019
proc = SPI_processed;
29893020
/*
2990-
* If this is a SELECT and some rows were fetched,
2991-
* then the rows are printed via elog(INFO).
3021+
* If some rows were fetched, print them via elog(INFO).
29923022
*/
2993-
if (ret == SPI_OK_SELECT && SPI_processed &gt; 0)
3023+
if (ret &gt; 0 &amp;&amp; SPI_tuptable != NULL)
29943024
{
29953025
TupleDesc tupdesc = SPI_tuptable-&gt;tupdesc;
29963026
SPITupleTable *tuptable = SPI_tuptable;
@@ -3005,7 +3035,7 @@ execq(text *sql, int cnt)
30053035
snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf), " %s%s",
30063036
SPI_getvalue(tuple, tupdesc, i),
30073037
(i == tupdesc-&gt;natts) ? " " : " |");
3008-
elog (INFO, "EXECQ: %s", buf);
3038+
elog(INFO, "EXECQ: %s", buf);
30093039
}
30103040
}
30113041

‎src/backend/executor/spi.c

Lines changed: 24 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.157 2006/08/14 22:57:15 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.158 2006/08/27 23:47:57 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1136,6 +1136,12 @@ SPI_result_code_string(int code)
11361136
return "SPI_OK_UPDATE";
11371137
case SPI_OK_CURSOR:
11381138
return "SPI_OK_CURSOR";
1139+
case SPI_OK_INSERT_RETURNING:
1140+
return "SPI_OK_INSERT_RETURNING";
1141+
case SPI_OK_DELETE_RETURNING:
1142+
return "SPI_OK_DELETE_RETURNING";
1143+
case SPI_OK_UPDATE_RETURNING:
1144+
return "SPI_OK_UPDATE_RETURNING";
11391145
}
11401146
/* Unrecognized code ... return something useful ... */
11411147
sprintf(buf, "Unrecognized SPI code %d", code);
@@ -1454,6 +1460,9 @@ _SPI_execute_plan(_SPI_plan *plan, Datum *Values, const char *Nulls,
14541460
{
14551461
ProcessUtility(queryTree->utilityStmt, paramLI,
14561462
dest, NULL);
1463+
/* Update "processed" if stmt returned tuples */
1464+
if (_SPI_current->tuptable)
1465+
_SPI_current->processed = _SPI_current->tuptable->alloced - _SPI_current->tuptable->free;
14571466
res = SPI_OK_UTILITY;
14581467
}
14591468
else
@@ -1542,13 +1551,22 @@ _SPI_pquery(QueryDesc *queryDesc, long tcount)
15421551
res = SPI_OK_SELECT;
15431552
break;
15441553
case CMD_INSERT:
1545-
res = SPI_OK_INSERT;
1554+
if (queryDesc->parsetree->returningList)
1555+
res = SPI_OK_INSERT_RETURNING;
1556+
else
1557+
res = SPI_OK_INSERT;
15461558
break;
15471559
case CMD_DELETE:
1548-
res = SPI_OK_DELETE;
1560+
if (queryDesc->parsetree->returningList)
1561+
res = SPI_OK_DELETE_RETURNING;
1562+
else
1563+
res = SPI_OK_DELETE;
15491564
break;
15501565
case CMD_UPDATE:
1551-
res = SPI_OK_UPDATE;
1566+
if (queryDesc->parsetree->returningList)
1567+
res = SPI_OK_UPDATE_RETURNING;
1568+
else
1569+
res = SPI_OK_UPDATE;
15521570
break;
15531571
default:
15541572
return SPI_ERROR_OPUNKNOWN;
@@ -1568,7 +1586,8 @@ _SPI_pquery(QueryDesc *queryDesc, long tcount)
15681586
_SPI_current->processed = queryDesc->estate->es_processed;
15691587
_SPI_current->lastoid = queryDesc->estate->es_lastoid;
15701588

1571-
if (operation == CMD_SELECT && queryDesc->dest->mydest == DestSPI)
1589+
if ((res == SPI_OK_SELECT || queryDesc->parsetree->returningList) &&
1590+
queryDesc->dest->mydest == DestSPI)
15721591
{
15731592
if (_SPI_checktuples())
15741593
elog(ERROR, "consistency check on SPI tuple count failed");

‎src/include/executor/spi.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
*
33
* spi.h
44
*
5-
* $PostgreSQL: pgsql/src/include/executor/spi.h,v 1.54 2006/07/11 18:26:11 momjian Exp $
5+
* $PostgreSQL: pgsql/src/include/executor/spi.h,v 1.55 2006/08/27 23:47:58 tgl Exp $
66
*
77
*-------------------------------------------------------------------------
88
*/
@@ -71,6 +71,9 @@ typedef struct
7171
#define SPI_OK_DELETE 8
7272
#define SPI_OK_UPDATE 9
7373
#define SPI_OK_CURSOR 10
74+
#define SPI_OK_INSERT_RETURNING 11
75+
#define SPI_OK_DELETE_RETURNING 12
76+
#define SPI_OK_UPDATE_RETURNING 13
7477

7578
extern DLLIMPORT uint32 SPI_processed;
7679
extern DLLIMPORT Oid SPI_lastoid;

‎src/pl/plperl/plperl.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
/**********************************************************************
22
* plperl.c - perl as a procedural language for PostgreSQL
33
*
4-
* $PostgreSQL: pgsql/src/pl/plperl/plperl.c,v 1.117 2006/08/13 17:31:10 momjian Exp $
4+
* $PostgreSQL: pgsql/src/pl/plperl/plperl.c,v 1.118 2006/08/27 23:47:58 tgl Exp $
55
*
66
**********************************************************************/
77

@@ -1630,7 +1630,7 @@ plperl_spi_execute_fetch_result(SPITupleTable *tuptable, int processed,
16301630
hv_store(result, "processed", strlen("processed"),
16311631
newSViv(processed), 0);
16321632

1633-
if (status == SPI_OK_SELECT)
1633+
if (status > 0 && tuptable)
16341634
{
16351635
AV *rows;
16361636
SV *row;

‎src/pl/plpgsql/src/pl_exec.c

Lines changed: 7 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.176 2006/08/15 19:01:17 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.177 2006/08/27 23:47:58 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -2370,23 +2370,16 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
23702370
case SPI_OK_INSERT:
23712371
case SPI_OK_UPDATE:
23722372
case SPI_OK_DELETE:
2373+
case SPI_OK_INSERT_RETURNING:
2374+
case SPI_OK_UPDATE_RETURNING:
2375+
case SPI_OK_DELETE_RETURNING:
23732376
Assert(stmt->mod_stmt);
23742377
exec_set_found(estate, (SPI_processed != 0));
23752378
break;
23762379

23772380
case SPI_OK_SELINTO:
2378-
Assert(!stmt->mod_stmt);
2379-
break;
2380-
23812381
case SPI_OK_UTILITY:
23822382
Assert(!stmt->mod_stmt);
2383-
/*
2384-
* spi.c currently does not update SPI_processed for utility
2385-
* commands. Not clear if this should be considered a bug;
2386-
* for the moment, work around it here.
2387-
*/
2388-
if (SPI_tuptable)
2389-
SPI_processed = (SPI_tuptable->alloced - SPI_tuptable->free);
23902383
break;
23912384

23922385
default:
@@ -2505,16 +2498,10 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
25052498
case SPI_OK_INSERT:
25062499
case SPI_OK_UPDATE:
25072500
case SPI_OK_DELETE:
2508-
break;
2509-
2501+
case SPI_OK_INSERT_RETURNING:
2502+
case SPI_OK_UPDATE_RETURNING:
2503+
case SPI_OK_DELETE_RETURNING:
25102504
case SPI_OK_UTILITY:
2511-
/*
2512-
* spi.c currently does not update SPI_processed for utility
2513-
* commands. Not clear if this should be considered a bug;
2514-
* for the moment, work around it here.
2515-
*/
2516-
if (SPI_tuptable)
2517-
SPI_processed = (SPI_tuptable->alloced - SPI_tuptable->free);
25182505
break;
25192506

25202507
case 0:

‎src/pl/plpython/plpython.c

Lines changed: 4 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
/**********************************************************************
22
* plpython.c - python as a procedural language for PostgreSQL
33
*
4-
* $PostgreSQL: pgsql/src/pl/plpython/plpython.c,v 1.85 2006/08/08 19:15:09 tgl Exp $
4+
* $PostgreSQL: pgsql/src/pl/plpython/plpython.c,v 1.86 2006/08/27 23:47:58 tgl Exp $
55
*
66
*********************************************************************
77
*/
@@ -2193,24 +2193,19 @@ PLy_spi_execute_fetch_result(SPITupleTable *tuptable, int rows, int status)
21932193
Py_DECREF(result->status);
21942194
result->status = PyInt_FromLong(status);
21952195

2196-
if (status == SPI_OK_UTILITY)
2197-
{
2198-
Py_DECREF(result->nrows);
2199-
result->nrows = PyInt_FromLong(0);
2200-
}
2201-
else if (status != SPI_OK_SELECT)
2196+
if (status > 0 && tuptable == NULL)
22022197
{
22032198
Py_DECREF(result->nrows);
22042199
result->nrows = PyInt_FromLong(rows);
22052200
}
2206-
else
2201+
else if (status > 0 && tuptable != NULL)
22072202
{
22082203
PLyTypeInfo args;
22092204
int i;
22102205

2211-
PLy_typeinfo_init(&args);
22122206
Py_DECREF(result->nrows);
22132207
result->nrows = PyInt_FromLong(rows);
2208+
PLy_typeinfo_init(&args);
22142209

22152210
oldcontext = CurrentMemoryContext;
22162211
PG_TRY();

‎src/pl/tcl/pltcl.c

Lines changed: 12 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
* pltcl.c - PostgreSQL support for Tcl as
33
* procedural language (PL)
44
*
5-
* $PostgreSQL: pgsql/src/pl/tcl/pltcl.c,v 1.106 2006/08/08 19:15:09 tgl Exp $
5+
* $PostgreSQL: pgsql/src/pl/tcl/pltcl.c,v 1.107 2006/08/27 23:47:58 tgl Exp $
66
*
77
**********************************************************************/
88

@@ -1663,10 +1663,6 @@ pltcl_process_SPI_result(Tcl_Interp *interp,
16631663

16641664
switch (spi_rc)
16651665
{
1666-
case SPI_OK_UTILITY:
1667-
Tcl_SetResult(interp, "0", TCL_VOLATILE);
1668-
break;
1669-
16701666
case SPI_OK_SELINTO:
16711667
case SPI_OK_INSERT:
16721668
case SPI_OK_DELETE:
@@ -1675,7 +1671,18 @@ pltcl_process_SPI_result(Tcl_Interp *interp,
16751671
Tcl_SetResult(interp, buf, TCL_VOLATILE);
16761672
break;
16771673

1674+
case SPI_OK_UTILITY:
1675+
if (tuptable == NULL)
1676+
{
1677+
Tcl_SetResult(interp, "0", TCL_VOLATILE);
1678+
break;
1679+
}
1680+
/* FALL THRU for utility returning tuples */
1681+
16781682
case SPI_OK_SELECT:
1683+
case SPI_OK_INSERT_RETURNING:
1684+
case SPI_OK_DELETE_RETURNING:
1685+
case SPI_OK_UPDATE_RETURNING:
16791686

16801687
/*
16811688
* Process the tuples we got

0 commit comments

Comments
 (0)