Dear Postgresql experts,
I'm writing a simple plpgsql function that selects a row from a table, modifies it slightly, and inserts the modified
version. This sounds simple, but I can't find how to do the insert in a simple generic fashion: although SELECT has a
formthat puts the result in a record variable, INSERT doesn't seem to have anything similar.
What I'd like to be able to write is something like this:
DECLARE R RECORD;
BEGIN SELECT * INTO R FROM TABLE WHERE id=n; R.something := x; INSERT INTO TABLE R;
END
But as far as I can see the only syntax that is allowed for INSERT is the normal INSERT INTO TABLE (x,y,z) VALUES
(a,b,c). To achieve what I want to do I'd need to iterate over the fields of the record (how?) and build up the string
ofthe INSERT query.
It seems odd that SELECT can use a record variable but INSERT can't, so I wonder if I have missed something. Any
suggestions?
Thanks in advance for any help anyone can offer.
Regards,
--Phil.