agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: Best way to store Master-Detail Data
Date: Thu, 13 Apr 2017 12:54:53 +0200
Message-ID: <20170413105453.GA4647@tux> (raw)
In-Reply-To: <BN6PR17MB13800E701DD70F7449956699E7020@BN6PR17MB1380.namprd17.prod.outlook.com>
References: <BN6PR17MB13800E701DD70F7449956699E7020@BN6PR17MB1380.namprd17.prod.outlook.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (4+ messages)  latest in thread

Message-ID: <20170413105453.GA4647@tux>
Permalink:  ../20170413105453.GA4647@tux/
Also on:    postgresql.org/message-id/20170413105453.GA4647@tux

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: akretschmer@spamfence.net
  Subject: Re: Best way to store Master-Detail Data
  In-Reply-To: <20170413105453.GA4647@tux>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox