From: Phil Endecott <spam_from_postgresql_sql@chezphil.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
To: Phil Endecott <spam_from_postgresql_sql@chezphil.org>
To: pgsql-sql@postgresql.org
Subject: Re: plpgsql - Insert from a record variable?
Date: Mon, 21 Jun 2004 13:23:33 -0400
Message-ID: <4357626@chezphil.org> (raw)
In-Reply-To: <17840.1087670348@sss.pgh.pa.us>
References: <20040619060330.83941.qmail@web13901.mail.yahoo.com>
<17840.1087670348@sss.pgh.pa.us>
Phil> Insert from a record variable?
Riccardo> Try insert into table select r.*;
Tom> in 7.5
Tom> insert into table select r.*;
Tom> insert into table values(r.*);
Tom> so long as r is declared to be of a named rowtype (not just
Tom> RECORD)
Thanks! Unfortunately I need record, rather than %rowtype. See my later email where I describe how I am trying to use this with inheritance; the function looks up a row in a base table, finds the derived table in which it really exists using pg_class.relname, and then inserts a modified copy of the row in the derived table.
I'm not concerned about the performance issues to do with pre-planning the queries. I think what I really need is an introspection mechanism so that I can loop over each element of the record and construct the insert as a string. Maybe this is possible using a different server-side language? I've just had an idea: perhaps rather than inspecting the record variable to see what fields it contains, I can look at the table to see what columns it contains (since this amounts to the same thing). Presumably I can do this using information_schema.columns. I'll have a go.
--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, tgl@sss.pgh.pa.us, abief_ag_-postgresql@yahoo.com
Subject: Re: plpgsql - Insert from a record variable?
In-Reply-To: <4357626@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