|
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----- 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: kgore4@xxxxxxxxxx |
