From: | "Hendra" <manusiatidakbiasa(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Array as parameter for stored procedure |
Date: | 2008-07-10 02:53:43 |
Message-ID: | 48757a0c.07506e0a.4ac4.ffff9880@mx.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bright D.L. | 2008-07-10 03:12:50 | Re: SELECT Query returns empty |
Previous Message | Klint Gore | 2008-07-10 02:41:15 | Re: SELECT Query returns empty |