Re: [SQL] Best way to store Master-Detail Data

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