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

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:kgore4(at)une(dot)edu(dot)au]
Sent: Wednesday, 20 August 2008 12:47
To: Dale Harris
Cc: pgsql-general(at)postgresql(dot)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: kgore4(at)une(dot)edu(dot)au