Commit8c78f8e

committed

Add PL/PgSQL FOUND and GET DIAGNOSTICS support for RETURN QUERY

statementPavel Stehule

1 parent78cbd49 commit8c78f8eCopy full SHA for 8c78f8e

File tree

+71

-2

lines changed

+71

-2

lines changed

Lines changed: 9 additions & 1 deletion

Original file line number	Diff line number	Diff 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 $ -->`
`2`	`2`
`3`	`3`	`<chapter id="plpgsql">`
`4`	`4`	`<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>`
`@@ -1356,6 +1356,14 @@ GET DIAGNOSTICS integer_var = ROW_COUNT;`
`1356`	`1356`	`execution of other statements within the loop body.`
`1357`	`1357`	`</para>`
`1358`	`1358`	`</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>`
`1359`	`1367`	`</itemizedlist>`
`1360`	`1368`
`1361`	`1369`	`<literal>FOUND</literal> is a local variable within each`

Lines changed: 6 additions & 1 deletion

Original file line number	Diff line number	Diff line change
`@@ -8,7 +8,7 @@`
`8`	`8`	`*`
`9`	`9`	`*`
`10`	`10`	`* 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 $`
`12`	`12`	`*`
`13`	`13`	`*-------------------------------------------------------------------------`
`14`	`14`	`*/`
`@@ -2286,6 +2286,7 @@ exec_stmt_return_query(PLpgSQL_execstate *estate,`
`2286`	`2286`	`PLpgSQL_stmt_return_query*stmt)`
`2287`	`2287`	`{`
`2288`	`2288`	`Portalportal;`
	`2289`	`+uint32processed=0;`
`2289`	`2290`
`2290`	`2291`	`if (!estate->retisset)`
`2291`	`2292`	`ereport(ERROR,`
`@@ -2327,6 +2328,7 @@ exec_stmt_return_query(PLpgSQL_execstate *estate,`
`2327`	`2328`	`HeapTupletuple=SPI_tuptable->vals[i];`
`2328`	`2329`
`2329`	`2330`	`tuplestore_puttuple(estate->tuple_store,tuple);`
	`2331`	`+processed++;`
`2330`	`2332`	`}`
`2331`	`2333`	`MemoryContextSwitchTo(old_cxt);`
`2332`	`2334`
`@@ -2336,6 +2338,9 @@ exec_stmt_return_query(PLpgSQL_execstate *estate,`
`2336`	`2338`	`SPI_freetuptable(SPI_tuptable);`
`2337`	`2339`	`SPI_cursor_close(portal);`
`2338`	`2340`
	`2341`	`+estate->eval_processed=processed;`
	`2342`	`+exec_set_found(estate,processed!=0);`
	`2343`	`+`
`2339`	`2344`	`returnPLPGSQL_RC_OK;`
`2340`	`2345`	`}`
`2341`	`2346`

Lines changed: 32 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -3666,3 +3666,35 @@ select * from tftest(10);`
`3666`	`3666`	`(2 rows)`
`3667`	`3667`
`3668`	`3668`	`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();`

Lines changed: 24 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -2948,3 +2948,27 @@ $$ language plpgsql immutable strict;`
`2948`	`2948`	`select*from tftest(10);`
`2949`	`2949`
`2950`	`2950`	`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`	`+`

Comments

(0)