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> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spam_from_postgresql_sql@chezphil.org
Subject: Re: plpgsql - Insert from a record variable?
In-Reply-To: <4899937@chezphil.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox