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

Commit8c78f8e

Browse files
committed
Add PL/PgSQL FOUND and GET DIAGNOSTICS support for RETURN QUERY
statementPavel Stehule
1 parent78cbd49 commit8c78f8e

File tree

4 files changed

+71
-2
lines changed

4 files changed

+71
-2
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.137 2009/02/04 21:30:41 alvherre Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.138 2009/02/05 15:25:49 momjian Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -1356,6 +1356,14 @@ GET DIAGNOSTICS integer_var = ROW_COUNT;
13561356
execution of other statements within the loop body.
13571357
</para>
13581358
</listitem>
1359+
<listitem>
1360+
<para>
1361+
A <command>RETURN QUERY</command> and <command>RETURN QUERY
1362+
EXECUTE</command> statements set <literal>FOUND</literal>
1363+
true if the query returns at least one row, false if no row
1364+
is returned.
1365+
</para>
1366+
</listitem>
13591367
</itemizedlist>
13601368

13611369
<literal>FOUND</literal> is a local variable within each

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

Lines changed: 6 additions & 1 deletion
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.231 2009/01/21 11:13:14 heikki Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.232 2009/02/05 15:25:49 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -2286,6 +2286,7 @@ exec_stmt_return_query(PLpgSQL_execstate *estate,
22862286
PLpgSQL_stmt_return_query*stmt)
22872287
{
22882288
Portalportal;
2289+
uint32processed=0;
22892290

22902291
if (!estate->retisset)
22912292
ereport(ERROR,
@@ -2327,6 +2328,7 @@ exec_stmt_return_query(PLpgSQL_execstate *estate,
23272328
HeapTupletuple=SPI_tuptable->vals[i];
23282329

23292330
tuplestore_puttuple(estate->tuple_store,tuple);
2331+
processed++;
23302332
}
23312333
MemoryContextSwitchTo(old_cxt);
23322334

@@ -2336,6 +2338,9 @@ exec_stmt_return_query(PLpgSQL_execstate *estate,
23362338
SPI_freetuptable(SPI_tuptable);
23372339
SPI_cursor_close(portal);
23382340

2341+
estate->eval_processed=processed;
2342+
exec_set_found(estate,processed!=0);
2343+
23392344
returnPLPGSQL_RC_OK;
23402345
}
23412346

‎src/test/regress/expected/plpgsql.out

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3666,3 +3666,35 @@ select * from tftest(10);
36663666
(2 rows)
36673667

36683668
drop function tftest(int);
3669+
create or replace function rttest()
3670+
returns setof int as $$
3671+
declare rc int;
3672+
begin
3673+
return query values(10),(20);
3674+
get diagnostics rc = row_count;
3675+
raise notice '% %', found, rc;
3676+
return query select * from (values(10),(20)) f(a) where false;
3677+
get diagnostics rc = row_count;
3678+
raise notice '% %', found, rc;
3679+
return query execute 'values(10),(20)';
3680+
get diagnostics rc = row_count;
3681+
raise notice '% %', found, rc;
3682+
return query execute 'select * from (values(10),(20)) f(a) where false';
3683+
get diagnostics rc = row_count;
3684+
raise notice '% %', found, rc;
3685+
end;
3686+
$$ language plpgsql;
3687+
select * from rttest();
3688+
NOTICE: t 2
3689+
NOTICE: f 0
3690+
NOTICE: t 2
3691+
NOTICE: f 0
3692+
rttest
3693+
--------
3694+
10
3695+
20
3696+
10
3697+
20
3698+
(4 rows)
3699+
3700+
drop function rttest();

‎src/test/regress/sql/plpgsql.sql

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2948,3 +2948,27 @@ $$ language plpgsql immutable strict;
29482948
select*from tftest(10);
29492949

29502950
dropfunction tftest(int);
2951+
2952+
create or replacefunctionrttest()
2953+
returns setofintas $$
2954+
declare rcint;
2955+
begin
2956+
return queryvalues(10),(20);
2957+
get diagnostics rc= row_count;
2958+
raise notice'% %', found, rc;
2959+
return queryselect*from (values(10),(20)) f(a)where false;
2960+
get diagnostics rc= row_count;
2961+
raise notice'% %', found, rc;
2962+
return query execute'values(10),(20)';
2963+
get diagnostics rc= row_count;
2964+
raise notice'% %', found, rc;
2965+
return query execute'select * from (values(10),(20)) f(a) where false';
2966+
get diagnostics rc= row_count;
2967+
raise notice'% %', found, rc;
2968+
end;
2969+
$$ language plpgsql;
2970+
2971+
select*from rttest();
2972+
2973+
dropfunction rttest();
2974+

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp