Search Postgresql Archives

Array as parameter for stored procedure

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux