Thread: problem with FOUND and EXECUTE in pl/pgsql

problem with FOUND and EXECUTE in pl/pgsql

From
Oleg Bartunov
Date:
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

Re: problem with FOUND and EXECUTE in pl/pgsql

From
Ries van Twisk
Date:
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







Re: problem with FOUND and EXECUTE in pl/pgsql

From
Tom Lane
Date:
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

Re: problem with FOUND and EXECUTE in pl/pgsql

From
Oleg Bartunov
Date:
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

Re: problem with FOUND and EXECUTE in pl/pgsql

From
Pavel Stehule
Date:
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
>