Hi,
I'm learning some 3-tier concept here and
very interested with postgresql stored-procedure
But since I 'm the type who
learn from example, I'm having some difficulties here
I want to know how
postgresql use array as parameter for stored procedure,
I imagine a case when
we want to save selling-transaction
we would do 2 process,
1. save our
customer, date of transaction, etc
2. save details of transaction like goods
we sell, qty, price etc
I'm thinking something looks like
this
Create function saveSellData(id_cust int, thisday timestamp,
id_goodies int[], qty[], prices[]) return void as $$
declare
id_trans int;
i int := 0;
begin
begin work;
--
save transaction master data // lets just pretend the primary key is a serial
type field
insert into selling (customer_id,
transaction_date) values (id_cust, thisday);
-- save transaction
detail data
id_trans := ?? -- what is command to get last inserted
transaction id?
loop ?? -- I don't know how to loop the
array
insert into selling_detail values (id_trans,
id_goodies[i], qty[i], prices[i]);
i := i +1;
end loop;
commit work;
end;
$$ language 'plpgsql';
I
haven't try this yet, since I lack of knowledge to finish this code
Can
anyone help me?
Or is there a better solution for this case?
Thank
you
Regards,
Hendra