Thread: problem with FOUND and EXECUTE in pl/pgsql
Hi there,
seems I don't understand how FOUND variable in pl/pgsql function defined,
when I use EXECUTE of PERFORM. There is no problem when I use plain SQL.
Below is a test I did for 8.4beta2. This is simplified script and I can
use plain SQL, but in my project I need EXECUTE.
CREATE TABLE db (a INT, b TEXT);
INSERTYTT INTO db VALUES(1,'one');
CREATE OR REPLACE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
EXECUTE 'UPDATE db SET b='||quote_literal(data)||' WHERE a='||key;
RAISE NOTICE 'found:%',FOUND;
END;
$$
LANGUAGE plpgsql;
After successfull update I expected TRUE, as with plain INSERT
(instead of EXECUTE), but FOUND is FALSE !
=# SELECT merge_db(1, 'two');
NOTICE: found:f
merge_db
----------
(1 row)
=# select * from db;
a | b
---+-----
1 | two
(1 row)
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Are you not confused somewhere?? First you insert INSERT INTO db VALUES(1,'one'); Then you do this : SELECT merge_db(1, 'two'); But for some reason this SQL select * from db; selects two for your text field... Ries On Jun 3, 2009, at 2:42 PM, Oleg Bartunov wrote: > Hi there, > > seems I don't understand how FOUND variable in pl/pgsql function > defined, > when I use EXECUTE of PERFORM. There is no problem when I use plain > SQL. > Below is a test I did for 8.4beta2. This is simplified script and I > can use plain SQL, but in my project I need EXECUTE. > > CREATE TABLE db (a INT, b TEXT); > INSERTYTT INTO db VALUES(1,'one'); > > CREATE OR REPLACE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID > AS > $$ > BEGIN > EXECUTE 'UPDATE db SET b='||quote_literal(data)||' WHERE > a='||key; > RAISE NOTICE 'found:%',FOUND; > END; > $$ > LANGUAGE plpgsql; > > > After successfull update I expected TRUE, as with plain INSERT > (instead of EXECUTE), but FOUND is FALSE ! > > =# SELECT merge_db(1, 'two'); > NOTICE: found:f > merge_db ---------- > > (1 row) > =# select * from db; > a | b ---+----- > 1 | two > (1 row) > > > > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general regards, Ries van Twisk ------------------------------------------------------------------------------------------------- Ries van Twisk tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS WebORB PostgreSQL DB-Architect email: ries@vantwisk.nl web: http://www.rvantwisk.nl/ skype: callto://r.vantwisk Phone: +1-810-476-4196 SIP: +1-747-690-5133
Oleg Bartunov <oleg@sai.msu.su> writes:
> seems I don't understand how FOUND variable in pl/pgsql function defined,
> when I use EXECUTE of PERFORM. There is no problem when I use plain SQL.
EXECUTE doesn't affect FOUND, even if the statement-to-be-executed would
have. There's been some discussion about changing that, but no
movement.
regards, tom lane
On Wed, 3 Jun 2009, Tom Lane wrote:
> Oleg Bartunov <oleg@sai.msu.su> writes:
>> seems I don't understand how FOUND variable in pl/pgsql function defined,
>> when I use EXECUTE of PERFORM. There is no problem when I use plain SQL.
>
> EXECUTE doesn't affect FOUND, even if the statement-to-be-executed would
> have. There's been some discussion about changing that, but no
> movement.
I think this should be clarified in docs.
Hmm, simple update-or-insert function in case of EXECUTE became
complicated. I had to use RETURNING xxx INTO yyy and check yyy for NULL
value.
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
2009/6/4 Oleg Bartunov <oleg@sai.msu.su>: > On Wed, 3 Jun 2009, Tom Lane wrote: > >> Oleg Bartunov <oleg@sai.msu.su> writes: >>> >>> seems I don't understand how FOUND variable in pl/pgsql function defined, >>> when I use EXECUTE of PERFORM. There is no problem when I use plain SQL. >> >> EXECUTE doesn't affect FOUND, even if the statement-to-be-executed would >> have. There's been some discussion about changing that, but no >> movement. > > I think this should be clarified in docs. Hmm, simple update-or-insert > function in case of EXECUTE became > complicated. I had to use RETURNING xxx INTO yyy and check yyy for NULL > value. use GET DIAGNOSTICS postgres=# create table ff(a integer); CREATE TABLE Time: 130,222 ms postgres=# create or replace function t(int) returns void as $$ declare r integer; begin execute 'insert into f values($1)' using $1; get diagnostics r = ROW_COUNT; raise notice '%', r; return; end; $$ language plpgsql; CREATE FUNCTION Time: 129,132 ms postgres=# select t(29); NOTICE: 1 t --- (1 row) Time: 51,979 ms postgres=# Regards Pavel Stehule > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >