Array as parameter for stored procedure
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
Hello
a)
create or replace function iterate(a int[])
returns void as $$
begin
for i in array_lower(a,1)..arry_upper(a,1) loop
raise notice '%', a[i];
end loop;
end;
$$ language plpgsql strict;
look to: http://www.pgsql.cz/index.php/PL/pgSQL_%28en%29 or
http://www.postgresql.org/docs/8.3/interactive/plpgsql.html
b) you can't explicitly specify begin and end of transaction inside
PostgreSQL function. This feature isn't supported.
create or replace function foo(a int)
returns void as $$
begin
for i in 1..a loop
insert into footab values(i);
end loop;
end;
$$ language plpgsql strict;
just -> create table footab(a int); select foo(10);
Regards
Pavel Stehule
2008/7/10 Hendra <manusiatidakbiasa@gmail.com>:
Show quoted text
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
hereI 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 etcI'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