I am sending patch, that adds FOUND and GET DIAGNOSTICS support for
RETURN QUERY statement
Regards
Pavel Stehule
2008/11/10 Andrew Gierth <[EMAIL PROTECTED]>:
>>>>>> "Pavel" == "Pavel Stehule" <[EMAIL PROTECTED]> writes:
>
> >> Well, changing the semantics of an already-released statement
> >> carries a risk of breaking existing apps that aren't expecting it
> >> to change FOUND. So I'd want to see a pretty strong case why this
> >> is important --- not just that it didn't meet someone's
> >> didn't-read-the-manual expectation.
>
> Pavel> It's should do some problems, but I belive much less than
> Pavel> change of casting or tsearch2 integration. And actually it's
> Pavel> not ortogonal. Every not dynamic statement change FOUND
> Pavel> variable.
>
> Regardless of what you think of FOUND, a more serious problem is this:
>
> postgres=# create function test(n integer) returns setof integer language
> plpgsql
> as $f$
> declare
> rc bigint;
> begin
> return query (select i from generate_series(1,n) i);
> get diagnostics rc = row_count;
> raise notice 'rc = %',rc;
> end;
> $f$;
> CREATE FUNCTION
> postgres=# select test(3);
> NOTICE: rc = 0
> test
> ------
> 1
> 2
> 3
> (3 rows)
>
> Since GET DIAGNOSTICS is documented as working for every SQL query
> executed in the function, rather than for a specific list of
> constructs, this is clearly a bug.
>
> --
> Andrew (irc:RhodiumToad)
>
> --
> Sent via pgsql-bugs mailing list ([EMAIL PROTECTED])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
*** ./doc/src/sgml/plpgsql.sgml.orig 2008-11-13 11:44:57.000000000 +0100
--- ./doc/src/sgml/plpgsql.sgml 2008-11-13 11:48:39.000000000 +0100
***************
*** 1356,1361 ****
--- 1356,1368 ----
execution of other statements within the loop body.
</para>
</listitem>
+ <listitem>
+ <para>
+ A <command>RETURN QUERY</command> and <command>RETURN QUERY
+ EXECUTE</command> statements sets <literal>FOUND</literal>
+ true if query returns least one row.
+ </para>
+ </listitem>
</itemizedlist>
<literal>FOUND</literal> is a local variable within each
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2008-11-13 10:53:37.000000000 +0100
--- ./src/pl/plpgsql/src/pl_exec.c 2008-11-13 11:29:24.000000000 +0100
***************
*** 2285,2290 ****
--- 2285,2291 ----
PLpgSQL_stmt_return_query *stmt)
{
Portal portal;
+ uint32 processed = 0;
if (!estate->retisset)
ereport(ERROR,
***************
*** 2326,2331 ****
--- 2327,2333 ----
HeapTuple tuple = SPI_tuptable->vals[i];
tuplestore_puttuple(estate->tuple_store, tuple);
+ processed++;
}
MemoryContextSwitchTo(old_cxt);
***************
*** 2335,2340 ****
--- 2337,2345 ----
SPI_freetuptable(SPI_tuptable);
SPI_cursor_close(portal);
+ estate->eval_processed = processed;
+ exec_set_found(estate, processed != 0);
+
return PLPGSQL_RC_OK;
}
*** ./src/test/regress/expected/plpgsql.out.orig 2008-11-13 11:44:34.000000000 +0100
--- ./src/test/regress/expected/plpgsql.out 2008-11-13 11:42:56.000000000 +0100
***************
*** 3666,3668 ****
--- 3666,3700 ----
(2 rows)
drop function tftest(int);
+ create or replace function rttest()
+ returns setof int as $$
+ declare rc int;
+ begin
+ return query values(10),(20);
+ get diagnostics rc = row_count;
+ raise notice '% %', found, rc;
+ return query select * from (values(10),(20)) f(a) where false;
+ get diagnostics rc = row_count;
+ raise notice '% %', found, rc;
+ return query execute 'values(10),(20)';
+ get diagnostics rc = row_count;
+ raise notice '% %', found, rc;
+ return query execute 'select * from (values(10),(20)) f(a) where false';
+ get diagnostics rc = row_count;
+ raise notice '% %', found, rc;
+ end;
+ $$ language plpgsql;
+ select * from rttest();
+ NOTICE: t 2
+ NOTICE: f 0
+ NOTICE: t 2
+ NOTICE: f 0
+ rttest
+ --------
+ 10
+ 20
+ 10
+ 20
+ (4 rows)
+
+ drop function rttest();
*** ./src/test/regress/sql/plpgsql.sql.orig 2008-11-13 11:32:17.000000000 +0100
--- ./src/test/regress/sql/plpgsql.sql 2008-11-13 11:41:20.000000000 +0100
***************
*** 2948,2950 ****
--- 2948,2974 ----
select * from tftest(10);
drop function tftest(int);
+
+ create or replace function rttest()
+ returns setof int as $$
+ declare rc int;
+ begin
+ return query values(10),(20);
+ get diagnostics rc = row_count;
+ raise notice '% %', found, rc;
+ return query select * from (values(10),(20)) f(a) where false;
+ get diagnostics rc = row_count;
+ raise notice '% %', found, rc;
+ return query execute 'values(10),(20)';
+ get diagnostics rc = row_count;
+ raise notice '% %', found, rc;
+ return query execute 'select * from (values(10),(20)) f(a) where false';
+ get diagnostics rc = row_count;
+ raise notice '% %', found, rc;
+ end;
+ $$ language plpgsql;
+
+ select * from rttest();
+
+ drop function rttest();
+
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
