Re: Best way to store Master-Detail Data

Alvin Díaz <alvin(dot)rd(at)live(dot)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