Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx]
Sent: Wednesday, 20 August 2008 12:47
To: Dale Harris
Cc: pgsql-general@xxxxxxxxxxxxxx
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@xxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux