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

Re: plpgsql - Insert from a record variable?

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

In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-06-19 18:50:23
Subject: Re: In 7.4 ensure you have DEFAULT now () with no spaces
Previous:From: Peter EisentrautDate: 2004-06-19 12:25:43
Subject: Re: In 7.4 ensure you have DEFAULT now () with no spaces

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