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