> 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.
r as alias for $1;
t as alias for $2;
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;
column_names := column_names || quote_ident(cr.column_name);
!! execute ''column_values :=
!! column_values || quote_literal(r.'' || cr.column_name || '')'';
execute ''insert into '' || t || ''('' || column_names ||
'') values ('' || column_values || '')'';
' language plpgsql;
In response to
pgsql-sql by date
|Next:||From: basic||Date: 2004-06-22 00:57:19|
|Subject: Re: plpgsql - Insert from a record variable?|
|Previous:||From: Phil Endecott||Date: 2004-06-21 17:23:33|
|Subject: Re: plpgsql - Insert from a record variable? |