Re: pgplsql, how to save row variable to a table row - Mailing list pgsql-general

Raymond O'Donnell wrote:
> On 26/03/2008 11:59, josep porres wrote:
>
>> row_tempf.field1 := value1;
>> row_tempf.field2 := value3;
>> ...
>> row_tempf.fieldN := valueN;
>>
>> -- NOW INSERT row_tempf  in the associated table
>> -- ???
>
> Easy! -
>
> insert into <tablename> ( <column> ... )
>   values (row_tempf.field1, row_tempf.field2, ... );

I've always tended to use:

INSERT INTO tablename SELECT rowvariable.* ;

It does have the downside that you need to set defaults yourself, eg
manually set a SERIAL column to nextval('sequence_name') ... but that's
not really a big deal.




eg:



CREATE TABLE demo_tab (
   id  SERIAL PRIMARY KEY,
   fd1 INTEGER,
   fd2 INTEGER
);

CREATE OR REPLACE FUNCTION demo_row_insert(INTEGER,INTEGER) RETURNS VOID
AS $$
DECLARE
   demo_tab_row demo_tab%rowtype;
   arg1 ALIAS FOR $1;
   arg2 ALIAS FOR $2;
BEGIN
   demo_tab_row.id := nextval('demo_tab_id_seq');
   demo_tab_row.fd1 := arg1;
   demo_tab_row.fd2 := arg2;
   INSERT INTO demo_tab SELECT demo_tab_row.*;
END;
$$ LANGUAGE 'plpgsql';




--
Craig Ringer