Content-Disposition: inline
Content-Transfer-Encoding: binary
Content-Type: text/plain
MIME-Version: 1.0
From: "Phil Endecott" <spam_from_postgresql_sql@chezphil.org>
To: pgsql-sql@postgresql.org
Subject: plpgsql - Insert from a record variable?
Date: Fri, 11 Jun 2004 10:42:09 -0400
Message-Id: <4899937@chezphil.org>

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.