hi,
I met a weird while using perform and execute in plpgsql.
the phenomena:
it seems PERFORM won't affect result status while doing
SELECT ... query, but EXECUTE do.I think the docs should
make it more clear if it's true.
from the docs for PERFORUM:
" This executes a SELECT query and discards the result. PL/pgSQL
variables are substituted in the query as usual. Also, the special
variable FOUND is set to true if the query produced at least one row, or
false if it produced no rows."
I think I should FOUND variable should be false in the example
below, but, my test shows me the opposite.
my version:
laser_uni=# select version();
version
---------------------------------------------------------------------------
PostgreSQL 7.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.1
(1 row)
here my test code:
---------------------------8<---------------------------------------------
drop table test;
create table test (t text);
CREATE or replace FUNCTION test()
RETURNS text
AS '
DECLARE
prefix text;
maxint integer;
ret text;
qry text;
rows integer;
BEGIN
prefix := cast(date_part(\'year\', now())::integer - 1911 as
text) || lpad(date_part(\'month\', now()), 2, \'0\');
qry := ''SELECT t FROM test where t LIKE '' ||
quote_literal(prefix) ||
''||'' ||
quote_literal(''%'') ||
'' limit 1'';
raise notice '' SQL: % '', qry;
-- execute qry;
perform qry;
get diagnostics rows = ROW_COUNT;
raise notice '' rows: % '', rows;
IF FOUND
THEN
raise notice ''found'';
ret = ''found'';
ELSE
raise notice ''not found'';
ret = ''not found'';
END IF;
RETURN ret;
END;'
LANGUAGE 'plpgsql';
----------------------------8<---------------------------------------------
regards laser
