On Sun, 30 Mar 2008 21:40:52 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ivan Sergio Borgonovo <mail@webthatworks.it> 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