As per the original message:
>I've got some code which postgres 8.3.3 won't accept. Postgres doesn't like the INTO clause on RETURNING INTO and I've tried following the documentation. > >UPDATE "EntityRelation" SET "Status" = inStatus, "Modified" = Session_TimeStamp(), "ModifiedBy" = UserID() WHERE ("RelationID" = inRelationID) AND ("EntityID" = inEnityID) AND IsEqual(inRelatedID, "RelatedID") RETURNING "Default" INTO oldDefault; > >Does anyone have any ideas if the INTO clause actually works at all for an UPDATE statement? And documentation link which advises that the UPDATE statement should be able to return a value into a variable in plpgsql. http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html The query above is out of my plpgsql script and the WHERE clause selects an unique record. Therefore only 1 value should ever be returned. The point is that I don't even get that far as the script fails to compile due to the INTO clause. Regards, Dale. -----Original Message----- From: Klint Gore [mailto:[EMAIL PROTECTED] Sent: Wednesday, 20 August 2008 12:47 To: Dale Harris Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] can't get UPDATE ... RETURNING ... INTO ... to compile successfully Dale Harris wrote: > It works for the INSERT command, but not UPDATE. For the INSERT command, it > makes my code look neater and I image it's more efficient too. > > This time I am trying to UPDATE a field using a primary key, and return > another field into a variable so that I can take necessary action if > required later in the plpgsql script. I know that I can issue another > SELECT query to retrieve the information, but I would have thought it would > be a lot more efficient to return the value during the UPDATE. > Works for me test=# begin; BEGIN test=# test=# create table foo(f1 int, f2 text); CREATE TABLE test=# insert into foo values(1, 'hi'); INSERT 0 1 test=# insert into foo values(2, 'hello'); INSERT 0 1 test=# test=# create function bar(int,int) returns boolean as $$ test$# declare test$# r record; test$# begin test$# update foo set f1 = $2 where f1 = $1 returning * into r; test$# raise notice '% %',r.f1,r.f2; test$# return true; test$# end;$$ language plpgsql volatile; CREATE FUNCTION test=# test=# create function bar1(text) returns boolean as $$ test$# declare test$# r record; test$# begin test$# for r in test$# update foo set f2 = f2 || $1 returning * test$# loop test$# raise notice '% %',r.f1,r.f2; test$# end loop; test$# return true; test$# end; test$# $$ language plpgsql volatile; CREATE FUNCTION test=# test=# select * from bar(2,3); NOTICE: 3 hello bar ----- t (1 row) test=# test=# select * from bar1('!'); NOTICE: 1 hi! NOTICE: 3 hello! bar1 ------ t (1 row) test=# -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED]