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

Commit3f8db37

Browse files
committed
Tweak SPI_cursor_open to allow INSERT/UPDATE/DELETE RETURNING; this was
merely a matter of fixing the error check, since the underlying Portalinfrastructure already handles it. This in turn allows these statementsto be used in some existing plpgsql and plperl contexts, such as aplpgsql FOR loop. Also, do some marginal code cleanup in places thatwere being sloppy about distinguishing SELECT from SELECT INTO.
1 parent883f4b4 commit3f8db37

File tree

16 files changed

+152
-114
lines changed

16 files changed

+152
-114
lines changed

‎doc/src/sgml/plperl.sgml

Lines changed: 20 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.55 2006/05/30 11:40:21 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.56 2006/08/12 20:05:54 tgl Exp $ -->
22

33
<chapter id="plperl">
44
<title>PL/Perl - Perl Procedural Language</title>
@@ -244,18 +244,8 @@ $$ LANGUAGE plperl;
244244

245245
SELECT * FROM perl_set();
246246
</programlisting>
247-
248247
</para>
249248

250-
<para>
251-
<application>PL/Perl</> does not currently have full support for
252-
domain types: it treats a domain the same as the underlying scalar
253-
type. This means that constraints associated with the domain will
254-
not be enforced. This is not an issue for function arguments, but
255-
it is a hazard if you declare a <application>PL/Perl</> function
256-
as returning a domain type.
257-
</para>
258-
259249
<para>
260250
If you wish to use the <literal>strict</> pragma with your code,
261251
the easiest way to do so is to <command>SET</>
@@ -439,26 +429,26 @@ SELECT * from lotsa_md5(500);
439429

440430
<para>
441431
The advantage of prepared queries is that is it possible to use one prepared plan for more
442-
than one query execution. After the plan is not needed anymore, itmust be freed with
432+
than one query execution. After the plan is not needed anymore, itmay be freed with
443433
<literal>spi_freeplan</literal>:
444434
</para>
445435

446436
<para>
447437
<programlisting>
448438
CREATE OR REPLACE FUNCTION init() RETURNS INTEGER AS $$
449-
$_SHARED{my_plan} = spi_prepare( 'SELECT (now() + $1)::date AS now', 'INTERVAL');
439+
$_SHARED{my_plan} = spi_prepare( 'SELECT (now() + $1)::date AS now', 'INTERVAL');
450440
$$ LANGUAGE plperl;
451441

452442
CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$
453-
return spi_exec_prepared(
454-
$_SHARED{my_plan},
455-
$_[0],
456-
)->{rows}->[0]->{now};
443+
return spi_exec_prepared(
444+
$_SHARED{my_plan},
445+
$_[0],
446+
)->{rows}->[0]->{now};
457447
$$ LANGUAGE plperl;
458448

459449
CREATE OR REPLACE FUNCTION done() RETURNS INTEGER AS $$
460-
spi_freeplan( $_SHARED{my_plan});
461-
undef $_SHARED{my_plan};
450+
spi_freeplan( $_SHARED{my_plan});
451+
undef $_SHARED{my_plan};
462452
$$ LANGUAGE plperl;
463453

464454
SELECT init();
@@ -478,16 +468,14 @@ SELECT done();
478468
</para>
479469

480470
<para>
481-
<literal>spi_cursor_close</literal> can beused to abort sequence of
482-
<literal>spi_fetchrow</literal> calls. Normally, the call to
483-
<literal>spi_fetchrow</literal> that returns <literal>undef</literal> is
484-
the signal that there are no more rows to read. Also
485-
that call automatically frees the cursor associated withthequery. If it is desired not
486-
to read all retuned rows, <literal>spi_cursor_close</literal> must be
487-
calledtoavoidmemory leaks.
471+
Normally, <function>spi_fetchrow</> should berepeated until it
472+
returns<literal>undef</literal>, indicating that there are no more
473+
rows to read. The cursor is automatically freed when
474+
<function>spi_fetchrow</> returns <literal>undef</literal>.
475+
If you do not wish to read alltherows, instead call
476+
<function>spi_cursor_close</> to free the cursor.
477+
Failuretodo so will result inmemory leaks.
488478
</para>
489-
490-
491479
</listitem>
492480
</varlistentry>
493481

@@ -630,8 +618,8 @@ CREATE FUNCTION badfunc() RETURNS integer AS $$
630618
return 1;
631619
$$ LANGUAGE plperl;
632620
</programlisting>
633-
The creation of this function will fail as its use of a forbidden
634-
operation will be be caught by the validator.
621+
The creation of this function will fail as its use of a forbidden
622+
operation will be be caught by the validator.
635623
</para>
636624

637625
<para>
@@ -748,8 +736,8 @@ $$ LANGUAGE plperl;
748736
<listitem>
749737
<para>
750738
Name of the table on which the trigger fired. This has been deprecated,
751-
and could be removed in a future release.
752-
Please use $_TD-&gt;{table_name} instead.
739+
and could be removed in a future release.
740+
Please use $_TD-&gt;{table_name} instead.
753741
</para>
754742
</listitem>
755743
</varlistentry>

‎doc/src/sgml/plpgsql.sgml

Lines changed: 17 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.97 2006/06/16 23:29:26 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.98 2006/08/12 20:05:54 tgl Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -2040,9 +2040,8 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
20402040
The <replaceable>target</replaceable> is a record variable, row variable,
20412041
or comma-separated list of scalar variables.
20422042
The <replaceable>target</replaceable> is successively assigned each row
2043-
resulting from the <replaceable>query</replaceable> (which must be a
2044-
<command>SELECT</command> command) and the loop body is executed for each
2045-
row. Here is an example:
2043+
resulting from the <replaceable>query</replaceable> and the loop body is
2044+
executed for each row. Here is an example:
20462045
<programlisting>
20472046
CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
20482047
DECLARE
@@ -2069,6 +2068,15 @@ $$ LANGUAGE plpgsql;
20692068
assigned row value is still accessible after the loop.
20702069
</para>
20712070

2071+
<para>
2072+
The <replaceable>query</replaceable> used in this type of <literal>FOR</>
2073+
statement can be any query that returns rows to the caller:
2074+
<command>SELECT</> (without <literal>INTO</>) is the most common case,
2075+
but you can also use <command>INSERT</>, <command>UPDATE</>, or
2076+
<command>DELETE</> with a <literal>RETURNING</> clause. Some utility
2077+
commands such as <command>EXPLAIN</> will work too.
2078+
</para>
2079+
20722080
<para>
20732081
The <literal>FOR-IN-EXECUTE</> statement is another way to iterate over
20742082
rows:
@@ -2078,12 +2086,11 @@ FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</r
20782086
<replaceable>statements</replaceable>
20792087
END LOOP <optional> <replaceable>label</replaceable> </optional>;
20802088
</synopsis>
2081-
This is like the previous form, except that the source
2082-
<command>SELECT</command> statement is specified as a string
2083-
expression, which is evaluated and replanned on each entry to
2084-
the <literal>FOR</> loop. This allows the programmer to choose the speed of
2085-
a preplanned query or the flexibility of a dynamic query, just
2086-
as with a plain <command>EXECUTE</command> statement.
2089+
This is like the previous form, except that the source query
2090+
is specified as a string expression, which is evaluated and replanned
2091+
on each entry to the <literal>FOR</> loop. This allows the programmer to
2092+
choose the speed of a preplanned query or the flexibility of a dynamic
2093+
query, just as with a plain <command>EXECUTE</command> statement.
20872094
</para>
20882095

20892096
<note>

‎doc/src/sgml/spi.sgml

Lines changed: 15 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.45 2006/03/10 19:10:49 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.46 2006/08/12 20:05:54 tgl Exp $ -->
22

33
<chapter id="spi">
44
<title>Server Programming Interface</title>
@@ -535,15 +535,15 @@ typedef struct
535535
<term><symbol>SPI_ERROR_TRANSACTION</symbol></term>
536536
<listitem>
537537
<para>
538-
if any command involving transaction manipulation was attempted
539-
(<command>BEGIN</>,
540-
<command>COMMIT</>,
541-
<command>ROLLBACK</>,
542-
<command>SAVEPOINT</>,
543-
<command>PREPARE TRANSACTION</>,
544-
<command>COMMIT PREPARED</>,
545-
<command>ROLLBACK PREPARED</>,
546-
or any variant thereof)
538+
if any command involving transaction manipulation was attempted
539+
(<command>BEGIN</>,
540+
<command>COMMIT</>,
541+
<command>ROLLBACK</>,
542+
<command>SAVEPOINT</>,
543+
<command>PREPARE TRANSACTION</>,
544+
<command>COMMIT PREPARED</>,
545+
<command>ROLLBACK PREPARED</>,
546+
or any variant thereof)
547547
</para>
548548
</listitem>
549549
</varlistentry>
@@ -917,10 +917,12 @@ bool SPI_is_cursor_plan(void * <parameter>plan</parameter>)
917917
<para>
918918
<function>SPI_is_cursor_plan</function> returns <symbol>true</symbol>
919919
if a plan prepared by <function>SPI_prepare</function> can be passed
920-
as an argument to <function>SPI_cursor_open</function> and <symbol>
921-
false</symbol> if that is not the case. The criteria are that the
920+
as an argument to <function>SPI_cursor_open</function>, or
921+
<symbol>false</symbol> if that is not the case. The criteria are that the
922922
<parameter>plan</parameter> represents one single command and that this
923-
command is a <command>SELECT</command> without an <command>INTO</command>
923+
command returns tuples to the caller; for example, <command>SELECT</>
924+
is allowed unless it contains an <literal>INTO</> clause, and
925+
<command>UPDATE</> is allowed only if it contains a <literal>RETURNING</>
924926
clause.
925927
</para>
926928
</refsect1>

‎src/backend/commands/portalcmds.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
*
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.49 2006/08/08 01:23:15 momjian Exp $
17+
* $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.50 2006/08/12 20:05:54 tgl Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -87,7 +87,7 @@ PerformCursorOpen(DeclareCursorStmt *stmt, ParamListInfo params)
8787

8888
if (query->into)
8989
ereport(ERROR,
90-
(errcode(ERRCODE_SYNTAX_ERROR),
90+
(errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
9191
errmsg("DECLARE CURSOR may not specify INTO")));
9292
if (query->rowMarks!=NIL)
9393
ereport(ERROR,

‎src/backend/executor/execMain.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,7 @@
2626
*
2727
*
2828
* IDENTIFICATION
29-
* $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.278 2006/08/1202:52:04 tgl Exp $
29+
* $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.279 2006/08/1220:05:55 tgl Exp $
3030
*
3131
*-------------------------------------------------------------------------
3232
*/
@@ -497,7 +497,7 @@ InitPlan(QueryDesc *queryDesc, int eflags)
497497
/*
498498
* if there is a result relation, initialize result relation stuff
499499
*/
500-
if (parseTree->resultRelation!=0&&operation!=CMD_SELECT)
500+
if (parseTree->resultRelation)
501501
{
502502
List*resultRelations=parseTree->resultRelations;
503503
intnumResultRelations;

‎src/backend/executor/functions.c

Lines changed: 13 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/functions.c,v 1.104 2006/07/14 14:52:19 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/functions.c,v 1.105 2006/08/12 20:05:55 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -361,7 +361,9 @@ postquel_getnext(execution_state *es)
361361
* run it to completion. (If we run to completion then
362362
* ExecutorRun is guaranteed to return NULL.)
363363
*/
364-
if (LAST_POSTQUEL_COMMAND(es)&&es->qd->operation==CMD_SELECT)
364+
if (LAST_POSTQUEL_COMMAND(es)&&
365+
es->qd->operation==CMD_SELECT&&
366+
es->qd->parsetree->into==NULL)
365367
count=1L;
366368
else
367369
count=0L;
@@ -868,7 +870,7 @@ check_sql_fn_retval(Oid func_id, Oid rettype, List *queryTreeList,
868870
JunkFilter**junkFilter)
869871
{
870872
Query*parse;
871-
intcmd;
873+
boolisSelect;
872874
List*tlist;
873875
ListCell*tlistitem;
874876
inttlistlen;
@@ -893,15 +895,18 @@ check_sql_fn_retval(Oid func_id, Oid rettype, List *queryTreeList,
893895
/* find the final query */
894896
parse= (Query*)lfirst(list_tail(queryTreeList));
895897

896-
cmd=parse->commandType;
897-
tlist=parse->targetList;
898+
/*
899+
* Note: eventually replace this with QueryReturnsTuples? We'd need
900+
* a more general method of determining the output type, though.
901+
*/
902+
isSelect= (parse->commandType==CMD_SELECT&&parse->into==NULL);
898903

899904
/*
900905
* The last query must be a SELECT if and only if return type isn't VOID.
901906
*/
902907
if (rettype==VOIDOID)
903908
{
904-
if (cmd==CMD_SELECT)
909+
if (isSelect)
905910
ereport(ERROR,
906911
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
907912
errmsg("return type mismatch in function declared to return %s",
@@ -911,7 +916,7 @@ check_sql_fn_retval(Oid func_id, Oid rettype, List *queryTreeList,
911916
}
912917

913918
/* by here, the function is declared to return some type */
914-
if (cmd!=CMD_SELECT)
919+
if (!isSelect)
915920
ereport(ERROR,
916921
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
917922
errmsg("return type mismatch in function declared to return %s",
@@ -921,6 +926,7 @@ check_sql_fn_retval(Oid func_id, Oid rettype, List *queryTreeList,
921926
/*
922927
* Count the non-junk entries in the result targetlist.
923928
*/
929+
tlist=parse->targetList;
924930
tlistlen=ExecCleanTargetListLength(tlist);
925931

926932
fn_typtype=get_typtype(rettype);

0 commit comments

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