Array as parameter for stored procedure

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

Responses

Browse pgsql-general by date

  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