[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] Best way to store Master-Detail Data
From: Alvin_Díaz <alvin.rd () live ! com>
Date: 2017-04-13 13:08:36
Message-ID: BN6PR17MB1380D9617207DFB1805C012FE7020 () BN6PR17MB1380 ! namprd17 ! prod ! outlook ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"><!-- P {margin-top:0;margin-bottom:0;} \
--></style> </head>
<body dir="ltr">
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:'Times \
New Roman', Times, serif;" dir="ltr"> <p>Hi Andreas thanks so much for your \
suggestion, but in that case i'll have to put all SQL under the program, thing</p> \
<p>that is not possible because i have tu put all business logic in the database, \
i'll show a example of what i</p> <p>do:</p>
<p><br>
</p>
<p></p>
<div>CREATE TABLE sales_invoice<br>
(<br>
id character varying(20) NOT NULL,<br>
customer_id character varying(20),<br>
branch_id integer,<br>
language_id integer,<br>
ncf character varying(19) NOT NULL,<br>
payment_term integer,<br>
payment_method integer,<br>
currency character varying(20),<br>
total_discount double precision,<br>
prompt_payment_discount_amount double precision,<br>
price_including_vat bit(1) NOT NULL,<br>
sales_invoice_date timestamp without time zone NOT NULL,<br>
create_by character varying(20),<br>
created_date timestamp without time zone NOT NULL,<br>
enable bit(1) NOT NULL,<br>
)</div>
<p></p>
<p><br>
</p>
<p></p>
<div>CREATE TABLE sales_invoice_lines<br>
(<br>
sales_invoice_id character varying(20) NOT NULL,<br>
line_no integer NOT NULL,<br>
item_id character varying(20),<br>
unit_of_measure integer,<br>
item_store integer,<br>
item_category integer,<br>
quantity double precision NOT NULL,<br>
price double precision NOT NULL,<br>
discount_amount double precision NOT NULL,<br>
vat_amount double precision NOT NULL,<br>
created_by character varying(20),<br>
created_date timestamp without time zone NOT NULL,<br>
enable bit(1) NOT NULL,</div>
)<br>
<p></p>
<p><br>
</p>
<p></p>
<div>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[])<br> RETURNS type_return_sales_invoice AS<br>
$BODY$<br>
DECLARE<br>
new_invoice_number varchar(20);<br>
new_ncf varchar(19);<br>
lines_quantity int:=0;<br>
total_amount double precision:=0; <br>
lines_transaction boolean:=false;<br>
entries_registered boolean:=false;<br>
f_apply_to_document_no varchar(20)[];<br>
f_amount_to_apply double precision [];<br>
return_values type_return_sales_invoice;<br>
BEGIN <br>
lines_quantity:= \
array_upper(f_item_id,array_ndims(f_item_id));<br> select * into \
new_invoice_number from getnexserialnumber(5);<br> select * into \
new_ncf from ncf.getnexncf(1,f_ncf_type);<br> <br>
return_values.document_no:=new_invoice_number;<br>
return_values.ncf:=new_ncf;<br>
insert into sales_invoice \
values(new_invoice_number,f_customer_id,f_branch_id,f_language_id,new_ncf,f_payment_term,<br>
f_payment_method,f_currency,f_total_discount,f_prompt_payment_disc \
ount_amount,f_price_including_vat,f_sale_invoice_date,f_create_by,current_timestamp,'B1'); \
<br> 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);<br> <br>
FOR i in 1..lines_quantity<br>
LOOP<br>
\
total_amount:=total_amount+((f_quantity[i]*f_unit_price[i])-f_discount_amount[i]+f_vat_amount[i]);<br>
END LOOP;<br>
\
total_amount:=total_amount-f_total_discount;<br> \
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);<br>
<br>
RETURN return_values;<br>
END;<br>
$BODY$<br>
LANGUAGE plpgsql VOLATILE<br>
COST 100;<br>
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[])<br> OWNER TO postgres;</div>
<p></p>
<p><br>
</p>
<p>As you can see there are many arrays parameters as much as fields in the detail \
table, the problem is that i dont want <br>
</p>
<p>to have a lot parameters.</p>
<p><br>
</p>
<p>Do you thing that is a good idea create a custom type and receive as \
parameter an array of that custom type?</p> <p><br>
</p>
<div id="Signature">
<div id="divtagdefaultwrapper" style="font-size:12pt; color:#000000; \
background-color:#FFFFFF; font-family:'Times New Roman',Times,serif"> <font \
color="#666666"><font size="3"><font face="Garamond"><b>Best regards<br> \
AEDG.</b></font></font></font> <div style="text-align:left"><b><font size="3" \
face="Garamond" color="#666666"><u style="background-color:rgb(255,255,255)"><br> \
</u></font></b></div> </div>
</div>
<br>
<div style="color: rgb(0, 0, 0);">
<div>
<div id="x_divRplyFwdMsg" dir="ltr"><font style="font-size:11pt" face="Calibri, \
sans-serif" color="#000000"><b>From:</b> pgsql-sql-owner@postgresql.org \
<pgsql-sql-owner@postgresql.org> on behalf of Andreas Kretschmer \
<akretschmer@spamfence.net><br> <b>Sent:</b> Thursday, April 13, 2017 6:54 \
AM<br> <b>To:</b> pgsql-sql@postgresql.org<br>
<b>Subject:</b> Re: [SQL] Best way to store Master-Detail Data</font>
<div> </div>
</div>
</div>
<font size="2"><span style="font-size:10pt;">
<div class="PlainText">Alvin Díaz <alvin.rd@live.com> wrote:<br>
<br>
> Hi.<br>
> <br>
> I wan to to know if someone can recommend me the best way to store <br>
> header and detail data<br>
> in the same function.<br>
> <br>
> For example:<br>
> <br>
> I have a table for purchase orders headers and a table for the detail <br>
> then i want to record<br>
> the header and detail under the same function to make sure that both <br>
> header and detail<br>
> are committed or not.<br>
> <br>
> <br>
> What i usually do is create a function with such as parameters as fields <br>
> in the header table and<br>
> after that, i add a same data type parameter for each field in the <br>
> detail but as an array.<br>
> <br>
> In the function, i insert the header data, after that i use a loop on <br>
> the first array parameter,<br>
> how each array parameter has the same length, i use the ordinal position <br>
> to insert the lines.<br>
<br>
As already suggested, you don't need a function for that, you can use<br>
begin and end to put all together in a transaction. Other solution: use<br>
writeable Common Table Expression (wCTE) like this example:<br>
<br>
test=# create table master(id serial primary key, name text);<br>
CREATE TABLE<br>
test=*# create table detail(master_id int references master, detail_text<br>
text);<br>
CREATE TABLE<br>
test=*# with new_master_id as (insert into master(name) values<br>
('master_new_value') returning id), new_details as (select 'detail1'<br>
union all select 'detail2') insert into detail select * from<br>
new_master_id cross join (select * from new_details) x;<br>
INSERT 0 2<br>
test=*# <br>
test=*# <br>
test=*# select * from master;<br>
id | \
name <br>
----+------------------<br>
1 | master_new_value<br>
(1 Zeile)<br>
<br>
test=*# select * from detail ;<br>
master_id | detail_text <br>
-----------+-------------<br>
1 | detail1<br>
1 | detail2<br>
(2 Zeilen)<br>
<br>
test=*# with new_master_id as (insert into master(name) values<br>
('master_new_value') returning id), new_details as (select 'detail11'<br>
union all select 'detail22') insert into detail select * from<br>
new_master_id cross join (select * from new_details) x;<br>
INSERT 0 2<br>
test=*# select * from detail ;<br>
master_id | detail_text <br>
-----------+-------------<br>
1 | detail1<br>
1 | detail2<br>
2 | detail11<br>
2 | detail22<br>
(4 Zeilen)<br>
<br>
test=*# <br>
<br>
<br>
As you can see, it is just one (in numbers: 1) Insert-Statement ;-)<br>
<br>
<br>
<br>
Regards, Andreas Kretschmer<br>
-- <br>
Andreas Kretschmer<br>
<a href="http://www.2ndQuadrant.com/" id="LPlnk958335" \
previewremoved="true">http://www.2ndQuadrant.com/</a><br> PostgreSQL Development, \
24x7 Support, Remote DBA, Training & Services<br> <br>
<br>
-- <br>
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)<br>
To make changes to your subscription:<br>
<a href="http://www.postgresql.org/mailpref/pgsql-sql" id="LPlnk782592" \
previewremoved="true">http://www.postgresql.org/mailpref/pgsql-sql</a><br> </div>
</span></font></div>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic