Re: insert rowtype

So, to answer my own question...

I wanted to duplicate a row, but change a few data items in it.
Here is an example table to demonstrate:

# select oid,* from test;

oid | id | tmstamp | description
-------+----+---------------------+-------------
20396 | 1 | 0000-00-00-00-00-00 | hello
20397 | 2 | 0000-00-00-00-00-00 | goodbye
20398 | 2 | 0000-00-00-00-00-01 | goodbye

I wanted to create a new row like this:

oid | id | tmstamp | description
-------+----+---------------------+-------------
20396 | 1 | 0000-00-00-00-00-00 | hello
20397 | 2 | 0000-00-00-00-00-00 | goodbye
20398 | 2 | 0000-00-00-00-00-01 | goodbye
20415 | 2 | 1111-00-00-00-00-02 | goodbye

But with my real table there are like 50 columns, and i didn't want to
retype all their names.
So, I created this function:

create or replace function updatetest(integer,varchar(32)) returns
integer as
'
declare
-- the oid of the new row
newoid integer;
begin
-- duplicate the row
insert into test
select * from test t where
t.tmstamp=(
select max(tt.tmstamp) from test tt where tt.id=t.id
)
and t.id=$1;

-- get the oid of the new duplicate
get diagnostics newoid = result_oid;

-- update the columns you want to change
update test set tmstamp=$2 where oid=newoid;

-- return the new oid
return newoid;
end;
' language plpgsql;

# select updatetest(2,'1111-00-00-00-00-02');

updatetest
------------
20415

I know it looks kinda obvious but it took me forever to figure it out.
Specifically, "get diagnostics newoid = result_oid;" is not very
obvious to plpgsql newbies.

-Bryan