On Sun, 30 Mar 2008 21:40:52 -0400 Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx> writes: > > for _row in > > select err, msg from errortable where err in (errcode) > > > where errcode is an array. > > That syntax doesn't work... > In recent PG releases it will work as "WHERE err = ANY (errcode)" > but note that there is *no* guarantee that the select will deliver > the rows in the same order the array elements are in. That's exactly what I was looking for. array_to_string is not as type safe as ANY and I didn't check how it may behave in a situation similar to: select * from array_to_string(ARRAY['ciao','pota\'z'],','); What I came up is create table errors (errcode int, errmsg varchar(255)); insert into errors values(1,'ciao1'); insert into errors values(2,'ciao2'); insert into errors values(3,'ciao3'); insert into errors values(4,'ciao4'); create or replace function auz(out _errcode int, out _errmsg text) returns setof record as $$ declare __errcode int[]; _row record; begin -- these should be function calls -- eg. __errcode[1]:=somechk(param1, param2); __errcode[1]:=1; __errcode[2]:=3; for _row in select errcode, errmsg from errors where errcode = any (__errcode) loop _errcode:=_row.errcode; _errmsg:=_row.errmsg; return next; end loop; return; end; $$ language plpgsql; I'm still thinking if this should be the way to report a list of failed tests. Maybe I could just hard code the error message in the checking function. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
