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@une.edu.au]
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: kgore4@une.edu.au