[GENERAL] pgplsql, how to save row variable to a table row

2008-03-26 Thread josep porres 
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_tempff2_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;

Re: [GENERAL] pgplsql, how to save row variable to a table row

2008-03-26 Thread Raymond O'Donnell 

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
[EMAIL PROTECTED]
---

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgplsql, how to save row variable to a table row

2008-03-26 Thread josep porres 
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



2008/3/26, Raymond O'Donnell [EMAIL PROTECTED]:

 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
 [EMAIL PROTECTED]
 ---

Re: [GENERAL] pgplsql, how to save row variable to a table row

2008-03-26 Thread Raymond O'Donnell 

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 nameshowever, 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
[EMAIL PROTECTED]
---

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgplsql, how to save row variable to a table row

2008-03-26 Thread Sam Mason 
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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgplsql, how to save row variable to a table row

2008-03-26 Thread Raymond O'Donnell 

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
[EMAIL PROTECTED]
---

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgplsql, how to save row variable to a table row

2008-03-26 Thread Craig Ringer 
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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgplsql, how to save row variable to a table row

2008-03-26 Thread josep porres 
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

Re: [GENERAL] pgplsql, how to save row variable to a table row

2008-03-26 Thread John DeSoi 


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.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general