Skip site navigation (1) Skip section navigation (2)

Re: plpgsql - Insert from a record variable?

From: "Phil Endecott" <spam_from_postgresql_sql(at)chezphil(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,"Riccardo G(dot) Facchini" <abief_ag_-postgresql(at)yahoo(dot)com>,pgsql-sql(at)postgresql(dot)org
Subject: Re: plpgsql - Insert from a record variable?
Date: 2004-06-21 18:51:04
Message-ID: 2179249@chezphil.org (view raw or flat)
Thread:
Lists: pgsql-sql
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.

In response to

Responses

pgsql-sql by date

Next:From: basicDate: 2004-06-22 00:57:19
Subject: Re: plpgsql - Insert from a record variable?
Previous:From: Phil EndecottDate: 2004-06-21 17:23:33
Subject: Re: plpgsql - Insert from a record variable?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group