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 form that 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 of the 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. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
