Thread: insert rowtype

In a plpgsql function, I have a variable of type table%rowtype
I populate the row by calling "select into <variable> * from <table>"
then i change some stuff like "<variable>.<field> := <new value>"
now i want to insert the row into the table as a new row - is there an 
easy way to do this?

i know i could do "insert into <table> values ( <variable>.<field 1>, 
<variable>.<field 2>, ... );"
but that is a lot of typing, and it seems like there should be an 
easier way.

-Bryan

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

>
> 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;
>
I only want to mention, this would insert multiple rows if the WHERE
conditions do not specifiy a single row.
Regards, Christoph