Re: Best way to store Master-Detail Data

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(at)postgresql(dot)org <pgsql-sql-owner(at)postgresql(dot)org> on behalf of Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
Sent: Thursday, April 13, 2017 6:54 AM
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] 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

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