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

[SQL] Best way to store Master-Detail Data

From

Alvin Díaz

Date:

13 April 2017, 09:51:36

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.

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

From

"David G. Johnston"

Date:

13 April 2017, 10:15:29

On Wednesday, April 12, 2017, Alvin Díaz <alvin.rd@live.com> wrote:

the same function to make sure that both
header and detail
are committed or not.

You don't need functions to establish transactions. That is why the commands BEGIN and COMMIT exist.

David J.

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

From

Andreas Kretschmer

Date:

13 April 2017, 16:54:53

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

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

From

Alvin Díaz

Date:

13 April 2017, 19:08:36

Hi Andreas thanks so much for your suggestion, but in that case i'll have to put all SQL under the program, thing

that is not possible because i have tu put all business logic in the database, i'll show a example of what i

do:

CREATE TABLE sales_invoice
(
id character varying(20) NOT NULL,
customer_id character varying(20),
branch_id integer,
language_id integer,
ncf character varying(19) NOT NULL,
payment_term integer,
payment_method integer,
currency character varying(20),
total_discount double precision,
prompt_payment_discount_amount double precision,
price_including_vat bit(1) NOT NULL,
sales_invoice_date timestamp without time zone NOT NULL,
create_by character varying(20),
created_date timestamp without time zone NOT NULL,
enable bit(1) NOT NULL,
)

CREATE TABLE sales_invoice_lines
(
sales_invoice_id character varying(20) NOT NULL,
line_no integer NOT NULL,
item_id character varying(20),
unit_of_measure integer,
item_store integer,
item_category integer,
quantity double precision NOT NULL,
price double precision NOT NULL,
discount_amount double precision NOT NULL,
vat_amount double precision NOT NULL,
created_by character varying(20),
created_date timestamp without time zone NOT NULL,
enable bit(1) NOT NULL,

)

CREATE OR REPLACE FUNCTION save_sale_invoice(f_customer_id character varying, f_ncf_type character varying, f_branch_id integer, f_language_id integer, f_payment_term integer, f_payment_method integer, f_currency character varying, f_total_discount double precision, f_prompt_payment_discount_amount double precision, f_price_including_vat bit, f_sale_invoice_date timestamp without time zone, f_create_by character varying, f_line_no integer[], f_item_id character varying[], f_unit_of_measure integer[], f_item_store integer[], f_quantity double precision[], f_unit_price double precision[], f_discount_amount double precision[], f_vat_amount double precision[])
RETURNS type_return_sales_invoice AS
$BODY$
DECLARE
new_invoice_number varchar(20);
new_ncf varchar(19);
lines_quantity int:=0;
total_amount double precision:=0;
lines_transaction boolean:=false;
entries_registered boolean:=false;
f_apply_to_document_no varchar(20)[];
f_amount_to_apply double precision [];
return_values type_return_sales_invoice;
BEGIN
   lines_quantity:= array_upper(f_item_id,array_ndims(f_item_id));
   select * into new_invoice_number from getnexserialnumber(5);
   select * into new_ncf from ncf.getnexncf(1,f_ncf_type);

   return_values.document_no:=new_invoice_number;
   return_values.ncf:=new_ncf;
   insert into sales_invoice values(new_invoice_number,f_customer_id,f_branch_id,f_language_id,new_ncf,f_payment_term,
   f_payment_method,f_currency,f_total_discount,f_prompt_payment_discount_amount,f_price_including_vat,f_sale_invoice_date,f_create_by,current_timestamp,'B1');
   select * into lines_transaction from save_sale_invoice_line(new_invoice_number , f_line_no , f_item_id, f_unit_of_measure,f_item_store,f_quantity,f_unit_price, f_discount_amount, f_vat_amount, f_create_by);

   FOR i in 1..lines_quantity
   LOOP
           total_amount:=total_amount+((f_quantity[i]*f_unit_price[i])-f_discount_amount[i]+f_vat_amount[i]);
   END LOOP;
       total_amount:=total_amount-f_total_discount;
        select * into entries_registered from save_customer_entry(f_customer_id,new_invoice_number,1,total_amount,f_sale_invoice_date,f_create_by,f_apply_to_document_no,f_amount_to_apply);

RETURN return_values;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION save_sale_invoice(character varying, character varying, integer, integer, integer, integer, character varying, double precision, double precision, bit, timestamp without time zone, character varying, integer[], character varying[], integer[], integer[], double precision[], double precision[], double precision[], double precision[])
OWNER TO postgres;

As you can see there are many arrays parameters as much as fields in the detail table, the problem is that i dont want

to have a lot parameters.

Do you thing that is a good idea create a custom type and receive as parameter an array of that custom type?

Best regards
AEDG.

From: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of Andreas Kretschmer <akretschmer@spamfence.net>
Sent: Thursday, April 13, 2017 6:54 AM
To: pgsql-sql@postgresql.org
Subject: 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

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