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
