Re: Problem with FOUND

Hi.
I run a function

CREATE OR REPLACE FUNCTION addRating(tbl_ INTEGER,value_ INTEGER)
RETURNS void AS $$
DECLARE
tablename TEXT;
fieldname TEXT;
BEGIN
tablename:='Rating_'||tbl_;
fieldname:='val';
EXECUTE 'UPDATE '||tablename||' SET '||fieldname||'='||value_||'
WHERE '||fieldname||'='||value_ ;
IF NOT FOUND THEN
EXECUTE 'INSERT INTO '||tablename||' ('||fieldname||') VALUES ('||value_||')';
END IF;
END;
$$ LANGUAGE plpgsql;

The UPDATE command works fine (afterwards the table is updated) but it
seems that it do the insert even if it do the update.
I thought this was a correct useage of "FOUND" so that it either do
the update or the insert, not both.
Can anyone help me spot the error?

"A B" <gentosaker(at)gmail(dot)com> writes:
> CREATE OR REPLACE FUNCTION addRating(tbl_ INTEGER,value_ INTEGER)
> RETURNS void AS $$
> DECLARE
> tablename TEXT;
> fieldname TEXT;
> BEGIN
> tablename:='Rating_'||tbl_;
> fieldname:='val';
> EXECUTE 'UPDATE '||tablename||' SET '||fieldname||'='||value_||'
> WHERE '||fieldname||'='||value_ ;
> IF NOT FOUND THEN
> EXECUTE 'INSERT INTO '||tablename||' ('||fieldname||') VALUES ('||value_||')';
> END IF;
> END;
> $$ LANGUAGE plpgsql;

> Can anyone help me spot the error?

EXECUTE doesn't set FOUND.

I think you'd be well advised to rethink your table layout so you don't
need so much dynamic SQL. The above is going to suck on both
performance and readability grounds, and it doesn't look like it's
accomplishing anything you couldn't do by combining all the Rating
tables into one table with an extra key column.

regards, tom lane

> I think you'd be well advised to rethink your table layout so you don't
> need so much dynamic SQL. The above is going to suck on both
> performance and readability grounds, and it doesn't look like it's
> accomplishing anything you couldn't do by combining all the Rating
> tables into one table with an extra key column.

Yes, it sucks, but I have to live with it right now (I've also removed
a lot of code from the function to make it more readable for you)
There are a lot of other parameters and execute commands :-(
Since I don't run >=8.2 I cant use FOR-EXECUTE-UPDATE-RETURNING.
So I will have to find another way.

But if UPDATE sets FOUND, what is the reason for EXECUTE not to set
FOUND if the query executed is an UPDATE?
Is it because it is impossible to tell in advance what kind of query
an EXECUTE statement will acctually execute?

2008/6/27 A B <gentosaker(at)gmail(dot)com>:
>> I think you'd be well advised to rethink your table layout so you don't
>> need so much dynamic SQL. The above is going to suck on both
>> performance and readability grounds, and it doesn't look like it's
>> accomplishing anything you couldn't do by combining all the Rating
>> tables into one table with an extra key column.
>
> Yes, it sucks, but I have to live with it right now (I've also removed
> a lot of code from the function to make it more readable for you)
> There are a lot of other parameters and execute commands :-(
> Since I don't run >=8.2 I cant use FOR-EXECUTE-UPDATE-RETURNING.
> So I will have to find another way.
>
> But if UPDATE sets FOUND, what is the reason for EXECUTE not to set
> FOUND if the query executed is an UPDATE?
> Is it because it is impossible to tell in advance what kind of query
> an EXECUTE statement will acctually execute?

compatibility with Oracle's PL/SQL. Internally isn't reason for it :(
try GET DIAGNOSTICS

postgres=# create table foox(a integer);
CREATE TABLE
postgres=# insert into foox values(10);
INSERT 0 1

postgres=# create function gg(v integer) returns void as $$
declare r integer;
begin execute 'update foox set a = ' || v || '
where a = ' || v;
get diagnostics r = row_count;
raise notice '%', r;
end; $$ language plpgsql;
CREATE FUNCTION
postgres=# select gg(11);
NOTICE: 0
gg
----

(1 row)

postgres=# select gg(10);
NOTICE: 1
gg
----

Regards
Pavel Stehule

>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Thanks for the suggestion on GET DIAGNOSTICS.

But concerning EXECUTE, if I do

BEGIN
EXECUTE QueryA
EXCEPTION WHEN OTHERS THEN
QueryB
END;

will it execute QueryB if QueryA fails?

2008/6/27 A B <gentosaker(at)gmail(dot)com>:
> Thanks for the suggestion on GET DIAGNOSTICS.
>
> But concerning EXECUTE, if I do
>
> BEGIN
> EXECUTE QueryA
> EXCEPTION WHEN OTHERS THEN
> QueryB
> END;
>
>
> will it execute QueryB if QueryA fails?

yes, but it's not preferable way. It creates subtransaction with some
(less) overhead.

Regards
Pavel
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>