Re: Array as parameter for stored procedure

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(at)gmail(dot)com>:
> 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