Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitea2e263

Browse files
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_RETURNINGstatements as well as utility statements that return tuples. Also,fix oversight that SPI_processed wasn't set for a utility statementreturning tuples. Per recent discussion.
1 parent7a2fe85 commitea2e263

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-
ifa <command>DELETE</command> was executed
469+
ifan <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-
ifan <command>INSERT</command> was executed
478+
ifa <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
caseSPI_OK_CURSOR:
11381138
return"SPI_OK_CURSOR";
1139+
caseSPI_OK_INSERT_RETURNING:
1140+
return"SPI_OK_INSERT_RETURNING";
1141+
caseSPI_OK_DELETE_RETURNING:
1142+
return"SPI_OK_DELETE_RETURNING";
1143+
caseSPI_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
caseCMD_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
caseCMD_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
caseCMD_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
returnSPI_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
#defineSPI_OK_DELETE8
7272
#defineSPI_OK_UPDATE9
7373
#defineSPI_OK_CURSOR10
74+
#defineSPI_OK_INSERT_RETURNING11
75+
#defineSPI_OK_DELETE_RETURNING12
76+
#defineSPI_OK_UPDATE_RETURNING13
7477

7578
externDLLIMPORTuint32SPI_processed;
7679
externDLLIMPORTOidSPI_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
caseSPI_OK_INSERT:
23712371
caseSPI_OK_UPDATE:
23722372
caseSPI_OK_DELETE:
2373+
caseSPI_OK_INSERT_RETURNING:
2374+
caseSPI_OK_UPDATE_RETURNING:
2375+
caseSPI_OK_DELETE_RETURNING:
23732376
Assert(stmt->mod_stmt);
23742377
exec_set_found(estate, (SPI_processed!=0));
23752378
break;
23762379

23772380
caseSPI_OK_SELINTO:
2378-
Assert(!stmt->mod_stmt);
2379-
break;
2380-
23812381
caseSPI_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
caseSPI_OK_INSERT:
25062499
caseSPI_OK_UPDATE:
25072500
caseSPI_OK_DELETE:
2508-
break;
2509-
2501+
caseSPI_OK_INSERT_RETURNING:
2502+
caseSPI_OK_UPDATE_RETURNING:
2503+
caseSPI_OK_DELETE_RETURNING:
25102504
caseSPI_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
case0:

‎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-
elseif (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+
elseif (status>0&&tuptable!=NULL)
22072202
{
22082203
PLyTypeInfoargs;
22092204
inti;
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-
caseSPI_OK_UTILITY:
1667-
Tcl_SetResult(interp,"0",TCL_VOLATILE);
1668-
break;
1669-
16701666
caseSPI_OK_SELINTO:
16711667
caseSPI_OK_INSERT:
16721668
caseSPI_OK_DELETE:
@@ -1675,7 +1671,18 @@ pltcl_process_SPI_result(Tcl_Interp *interp,
16751671
Tcl_SetResult(interp,buf,TCL_VOLATILE);
16761672
break;
16771673

1674+
caseSPI_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
caseSPI_OK_SELECT:
1683+
caseSPI_OK_INSERT_RETURNING:
1684+
caseSPI_OK_DELETE_RETURNING:
1685+
caseSPI_OK_UPDATE_RETURNING:
16791686

16801687
/*
16811688
* Process the tuples we got

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp