Re: PLPGSQL and FOUND stange behaviour after EXECUTE

Hello,
FreeBSD, I386, Postgres 8.0.0 beta2

Trying issuing such a plpgsql function:
Create function test_fun (suffix char(4)) returns integer as $$
DECLARE
sql text;
BEGIN
sql := 'insert into tbl' || suffix::text || ' values (1,1)';
EXECUTE sql;
IF NOT FOUND THEN
RAISE NOTICE 'NOT INSERTED';
END IF;
return 1;
END;
$$ LANGUAGE plpgsql;
create table tbl_a (id integer,name integer);

db# select test_fun('_a');
NOTICE: NOT INSERTED
test_fun
----------
1
(1 row)

db# # select * from tbl_a;
id | name
----+------
1 | 1
(1 row)

When I trying monitoring the result with GET DIAGNOSTICS - all fine, but
with FOUND it doesn't work.

Thank You in advance.

Konstantin

=?koi8-r?B?68/O09TBztTJzg==?= <beholder(at)mmska(dot)ru> writes:
> sql := 'insert into tbl' || suffix::text || ' values (1,1)';
> EXECUTE sql;
> IF NOT FOUND THEN
> RAISE NOTICE 'NOT INSERTED';
> END IF;

EXECUTE does not set the FOUND flag. See
http://developer.postgresql.org/docs/postgres/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

regards, tom lane

On Fri, 2004-10-01 at 02:26, Tom Lane wrote:
> EXECUTE does not set the FOUND flag.

Is there a good reason for this behavior?

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> On Fri, 2004-10-01 at 02:26, Tom Lane wrote:
>> EXECUTE does not set the FOUND flag.

> Is there a good reason for this behavior?

Possibly not. Can EXECUTE determine how the executed statement would
have set the flag? Should we assume that the function doing the EXECUTE
knows exactly what it's executing and what the implications on FOUND
ought to be?

regards, tom lane

On Fri, 2004-10-01 at 13:39, Tom Lane wrote:
> Possibly not. Can EXECUTE determine how the executed statement would
> have set the flag?

At the moment, EXECUTE just feeds the string it finds to spi_execute().
We could probably hack it to figure out how to modify FOUND, but I think
it would be ugly. One way to fix this would be to reimplement EXECUTE to
be essentially `eval': it would take an arbitrary string and execute it
as a PL/pgSQL statement. That would fix the FOUND problem, and also give
us EXECUTE INTO in one fell swoop. (Rather than reimplementing EXECUTE,
we might want to add this functionality as a new command -- "EVAL" might
be a good name for it.)

> Should we assume that the function doing the EXECUTE
> knows exactly what it's executing and what the implications on FOUND
> ought to be?

I think it's reasonable to assume that the application developer knows
this much.

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> ... One way to fix this would be to reimplement EXECUTE to
> be essentially `eval': it would take an arbitrary string and execute it
> as a PL/pgSQL statement. That would fix the FOUND problem, and also give
> us EXECUTE INTO in one fell swoop.

Yeah, this has been on my to-do list for awhile...

> (Rather than reimplementing EXECUTE, we might want to add this
> functionality as a new command -- "EVAL" might be a good name for it.)

That would give cover for the inevitable backward-compatibility
arguments anyway. One question here is whether Oracle's PL/SQL has a
precedent, and if so which way does it point?

regards, tom lane

Tom Lane wrote:
> Yeah, this has been on my to-do list for awhile...

Ah, ok. Is this something you want to handle, or should I take a look?

> One question here is whether Oracle's PL/SQL has a
> precedent, and if so which way does it point?

I did some limited testing of this, and it appears that PL/SQL's EXECUTE
IMMEDIATE modifies SQL%FOUND.

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> Tom Lane wrote:
>> Yeah, this has been on my to-do list for awhile...

> Ah, ok. Is this something you want to handle, or should I take a look?

Well, it's not *high* on my to-do list; feel free to take a look.

>> One question here is whether Oracle's PL/SQL has a
>> precedent, and if so which way does it point?

> I did some limited testing of this, and it appears that PL/SQL's EXECUTE
> IMMEDIATE modifies SQL%FOUND.

Hm, okay, then we should probably think about doing so too.

If the EXECUTE executes something that's not
SELECT/INSERT/UPDATE/DELETE, should it clear FOUND? Or leave it alone?

regards, tom lane