problem with FOUND and EXECUTE in pl/pgsql

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