Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully

Dale Harris wrote:
>
> 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.
>

Show us the whole function - then we can try it and see where the
problem is. What is the actual message you get?

Are you sure you spelled entity right in inEnityID (need another T
perhaps)?

Does select version() actually say 8.3.3?

The statement you posted works for me.

test=# begin;
BEGIN
test=#
test=# create table "EntityRelation"
test-# ("EntityID" int,
test(# "Status" int,
test(# "Modified" timestamp,
test(# "ModifiedBy" text,
test(# "RelationID" int,
test(# "RelatedID" int,
test(# "Default" text);
CREATE TABLE
test=#
test=# insert into "EntityRelation"
test-# values (1,1,now(), 'me', 1,1,'hello');
INSERT 0 1
test=#
test=# create or replace function foo() returns boolean as $$
test$# declare
test$# oldDefault text;
test$# instatus int = 1;
test$# inRelationID int = 1;
test$# inRelatedID int = 1;
test$# inEnityID int = 1;
test$#
test$# begin
test$#
test$# UPDATE "EntityRelation"
test$# SET "Status" = inStatus,
test$# "Modified" = now(),
test$# "ModifiedBy" =current_user
test$# WHERE ("RelationID" = inRelationID)
test$# AND ("EntityID" = inEnityID)
test$# AND inRelatedID = "RelatedID"
test$# RETURNING "Default"
test$# INTO oldDefault;
test$#
test$# raise notice '%', oldDefault;
test$# return false;
test$#
test$# end;$$ language plpgsql volatile;
CREATE FUNCTION
test=#
test=# select foo();
NOTICE: hello
foo
-----
f
(1 row)

test=#

klint.

--
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(at)une(dot)edu(dot)au