Date: Thu, 13 Apr 2017 12:54:53 +0200
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: Best way to store Master-Detail Data
Message-ID: <20170413105453.GA4647@tux>
References: 
 <BN6PR17MB13800E701DD70F7449956699E7020@BN6PR17MB1380.namprd17.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <BN6PR17MB13800E701DD70F7449956699E7020@BN6PR17MB1380.namprd17.prod.outlook.com>
User-Agent: Mutt/1.5.18 (2008-05-17)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


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