Thread: pgplsql, how to save row variable to a table row

Hi,


i'm learning how to work with rows (rowtype) , but I cannot find a way to insert one, once i filled up the fields, in a table.
the thing is: I have some rows filled up with execute commands  as you can see below.
Once I've filled the row_tempf, how can I insert this row to the table f2_tempfac? Can I use the execute command for that?

Thanks!

Josep Porres



DECLARE
    row_tfa      f2_tarifa_a%rowtype;
    row_tempf    f2_tempfac%rowtype;
    ...
BEGIN

        ...

        BEGIN
          EXECUTE 'SELECT * FROM F2_TARIFA_A
                   WHERE (TIPUS = ' || quote_literal(f2_Mtar) || ')
               AND ' || quote_literal(datafac) || ' BETWEEN DINICI AND DFINAL'
               INTO STRICT row_tfa;
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
              RAISE EXCEPTION 'TARIFA_A % no trobada.', f2_Mtar;
            WHEN TOO_MANY_ROWS THEN
              RAISE EXCEPTION 'TARIFA_A % no unica.', f2_Mtar;
        END;

        ...
   
        row_tempf.field1 := value1;
        row_tempf.field2 := value3;
        ...
        row_tempf.fieldN := valueN;

        -- NOW INSERT row_tempf  in the associated table
        -- ???

END;

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, ... );

:-)

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

I've tried:

EXECUTE 'INSERT INTO F2_TEMPFAC SELECT * FROM row_tempf';

but I've got not surprisingly:

ERROR: relation "row_tempf" does not exist
Estado SQL:42P01
Contexto:SQL statement "INSERT INTO F2_TEMPFAC SELECT * FROM row_tempf"
PL/pgSQL function "f2_facturar" line 437 at EXECUTE statement


I'm going to try your suggestion, however it's a bit annoying because this table has a lot of fields.
maybe i'm lazy to write all of them :P ...
well... hands on it!!!

thx

Josep

On 26/03/2008 12:14, josep porres wrote:

> I'm going to try your suggestion, however it's a bit annoying because
> this table has a lot of fields.
> maybe i'm lazy to write all of them :P ...

Heh heh.... I sometimes cut and paste SQL from the table definition
window in PgAdmin, to save typing the column names....however, by the
time I've finished reformatting it, typing them directly would have been
just as fast. :-)

Ray.


---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

On Wed, Mar 26, 2008 at 01:14:07PM +0100, josep porres wrote:
> I've tried:
>
> EXECUTE 'INSERT INTO F2_TEMPFAC SELECT * FROM row_tempf';

I think that:

  INSERT INTO f2_tempfac
    SELECT (row_tempf).*;

should do what you want.  Unless you're doing something very fancy you
don't want the execute either.  You can just put normal SQL statements
in the code and they'll get run as normal, expanding variables when
needed.


  Sam

On 26/03/2008 12:26, Sam Mason wrote:

>   INSERT INTO f2_tempfac
>     SELECT (row_tempf).*;

Does this depend on the column order in table f2_tempfac being the same
as that in the SELECT that produced row_tempf?

Or will row_tempf.foo automagically get matched with column
f2_tempfac.foo, etc?

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

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

INSERT INTO demo_tab SELECT demo_tab_row.*;


I tried this before with no success.
But I realized that it was because a problem with a constraint, no warnings about this at all.
With the detailed insert i've got the constraint error.
I was using the execute because I had a variable called the same as a field, not good, I changed it.
So now it's working perfectly!!!

Thanks a lot guys!


Josep Porres

On Mar 26, 2008, at 8:59 AM, josep porres wrote:

>
> INSERT INTO demo_tab SELECT demo_tab_row.*;
>
>
> I tried this before with no success.
> But I realized that it was because a problem with a constraint, no
> warnings about this at all.
> With the detailed insert i've got the constraint error.
> I was using the execute because I had a variable called the same as
> a field, not good, I changed it.
> So now it's working perfectly!!!


You don't need SELECT at all:

INSERT INTO demo_tab VALUES (demo_tab_row.*);


John DeSoi, Ph.D.