From: Tom Lane <tgl@sss.pgh.pa.us>
To: Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
Cc: Phil Endecott <spam_from_postgresql_sql@chezphil.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: plpgsql - Insert from a record variable?
Date: Sat, 19 Jun 2004 14:39:08 -0400
Message-ID: <17840.1087670348@sss.pgh.pa.us> (raw)
In-Reply-To: <20040619060330.83941.qmail@web13901.mail.yahoo.com>
References: <20040619060330.83941.qmail@web13901.mail.yahoo.com>
"Riccardo G. Facchini" <abief_ag_-postgresql@yahoo.com> writes:
> --- Phil Endecott <__> wrote:
>> 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.
> I'm not sure if it will work, but have you tried [snip]
> declare r record;
> insert into table select r.*;
I don't know of any way to handle this in existing releases ---
plpgsql's support for rowtype variables is just too limited.
However, in 7.5 you will be able to do either of
insert into table select r.*;
insert into table values(r.*);
so long as r is declared to be of a named rowtype (not just RECORD;
that doesn't have a known field list to plan the query with). For
instance I was just experimenting with this test case:
create or replace function fooey(int) returns void as '
declare r foo;
begin
select * into r from foo where id = $1;
r.f1 = ''baz'';
insert into foo select r.*;
return;
end' language plpgsql;
regards, tom lane
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: tgl@sss.pgh.pa.us, abief_ag_-postgresql@yahoo.com, spam_from_postgresql_sql@chezphil.org
Subject: Re: plpgsql - Insert from a record variable?
In-Reply-To: <17840.1087670348@sss.pgh.pa.us>
* 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