Обсуждение: plpgsql: inserting a record into a (matching) table

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

Brian Hurt <bhurt@janestcapital.com> writes:
> I'd like to do is something like (note that this does not work):

>     FOR t_rec IN
>         SELECT * from foo_table WHERE primary_key = old_key
>         t_rec.primary_key := new_key;
>         t_rec.other_column := new_value;
>         INSERT INTO foo_table VALUES ( t_rec ); -- Wrong- does not work

As of PG 8.2 I think that that will work if you say "t_rec.*" in the
VALUES.  As-is, it's asking the system to insert a composite datum into
the first column of the table.

            regards, tom lane