Re: 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