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@gmail.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