Alvin Díaz <alvin.rd@live.com> wrote:
> Hi.
>
> I wan to to know if someone can recommend me the best way to store
> header and detail data
> in the same function.
>
> For example:
>
> I have a table for purchase orders headers and a table for the detail
> then i want to record
> the header and detail under the same function to make sure that both
> header and detail
> are committed or not.
>
>
> What i usually do is create a function with such as parameters as fields
> in the header table and
> after that, i add a same data type parameter for each field in the
> detail but as an array.
>
> In the function, i insert the header data, after that i use a loop on
> the first array parameter,
> how each array parameter has the same length, i use the ordinal position
> to insert the lines.
As already suggested, you don't need a function for that, you can use
begin and end to put all together in a transaction. Other solution: use
writeable Common Table Expression (wCTE) like this example:
test=# create table master(id serial primary key, name text);
CREATE TABLE
test=*# create table detail(master_id int references master, detail_text
text);
CREATE TABLE
test=*# with new_master_id as (insert into master(name) values
('master_new_value') returning id), new_details as (select 'detail1'
union all select 'detail2') insert into detail select * from
new_master_id cross join (select * from new_details) x;
INSERT 0 2
test=*#
test=*#
test=*# select * from master;id | name
----+------------------ 1 | master_new_value
(1 Zeile)
test=*# select * from detail ;master_id | detail_text
-----------+------------- 1 | detail1 1 | detail2
(2 Zeilen)
test=*# with new_master_id as (insert into master(name) values
('master_new_value') returning id), new_details as (select 'detail11'
union all select 'detail22') insert into detail select * from
new_master_id cross join (select * from new_details) x;
INSERT 0 2
test=*# select * from detail ;master_id | detail_text
-----------+------------- 1 | detail1 1 | detail2 2 | detail11 2 | detail22
(4 Zeilen)
test=*#
As you can see, it is just one (in numbers: 1) Insert-Statement ;-)
Regards, Andreas Kretschmer
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
