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
