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: pgsql-sql@postgresql.org
Subject: Re: plpgsql - Insert from a record variable?
Date: Mon, 21 Jun 2004 14:51:04 -0400
Message-ID: <2179249@chezphil.org> (raw)
In-Reply-To: <4357626@chezphil.org>
References: <20040619060330.83941.qmail@web13901.mail.yahoo.com>
<17840.1087670348@sss.pgh.pa.us>
<4357626@chezphil.org>
I wrote:
> 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
This is my effort. It doesn't work, because I can only EXECUTE
SQL statements, not PLPGSQL statements. I need to EXECUTE an
assignment statement to accumulate the string of column values.
I have a feeling that I can EXECUTE a CREATE FUNCTION statement,
and then call the function, but this seems over-the-top. I just
want to insert a record into a table! Any better ideas?
-- Simply insert record r into table t.
-- Doesn't work, because EXECUTE takes an SQL command, not
-- a plpgsql statement.
create function insert_record ( record, text ) as '
-- probably ought to pass schema as well as table name, since
-- information_schema.columns query doesn't use search_path.
declare
r as alias for $1;
t as alias for $2;
cr information_schema.columns%rowtype;
first boolean;
column_names text;
column_values text;
begin
first := true;
for cr in select * from information_schema.columns
where table_name=t loop
if not first then
column_names := column_names || '', '';
column_values := column_values || '', '';
first := false;
end if;
column_names := column_names || quote_ident(cr.column_name);
!! execute ''column_values :=
!! column_values || quote_literal(r.'' || cr.column_name || '')'';
end loop;
execute ''insert into '' || t || ''('' || column_names ||
'') values ('' || column_values || '')'';
end;
' language plpgsql;
--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: <2179249@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