Thread: 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@gmail.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@gmail.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@postgresql.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@gmail.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >