plpgsql: inserting a record into a (matching) table

From: Brian Hurt <bhurt(at)janestcapital(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: plpgsql: inserting a record into a (matching) table
Date: 2007-03-13 18:41:49
Message-ID: 45F6F06D.4060207@janestcapital.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I'm wondering how to do the following: what I'd like to do is to select
a row out of a table, change some values (including the primary key),
and then insert the row back into the table- adding a new row with the
many of the same values as an already existing row. But I don't want to
have to specify all the columns of the table and spell things out. What
I'd like to do is something like (note that this does not work):

CREATE OR REPLACE FUNCTION foo(old_key TEXT, new_key TEXT) RETURNS void
AS $_$
DECLARE
t_rec RECORD;
BEGIN
FOR t_rec IN
SELECT * from foo_table WHERE primary_key = old_key
LOOP
t_rec.primary_key := new_key;
t_rec.other_column := new_value;
INSERT INTO foo_table VALUES ( t_rec ); -- Wrong- does not work
END LOOP;
END;
$_$ LANGUAGE plpsql;

Is there some way to do this, and I'm just being stupid and not seeing
it, or am I doomed to have to spell out all the column names in foo_table?

Brian

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Allan Sullivan 2007-03-13 19:57:57 Error 25P02 - transaction aborted - Potential causes?
Previous Message Sean Davis 2007-03-12 10:40:01 Re: PostgreSQL and import/export to dbf and mdb