Обсуждение: shortcut for select * where id in (arrayvar)

I'm still investigating on how to return array elements.

I came out with something like:

create or replace function auz(out _errcode int, out _errmsg text)
returns setof record as $$
declare
    errcode int[];
    errmsg text[];
    _row record;
begin
    errcode[1]:=1;
    errmsg[1]:='pota pota';
    errcode[2]:=3;
    errmsg[2]:='bau bau';
    for i IN coalesce(array_lower(errcode,1),0) ..
           coalesce(array_upper(errcode,1),-1)
         loop _errcode:=errcode[i];
        _errmsg:=errmsg[i]; return next;
    end loop;
    return;
end;
$$ language plpgsql;

I'd like to avoid to hard code error messages in the function so I
was thinking about adding a table with error_id, error_messages and
change the for loop with something similar to

for _row in
    select err, msg from errortable where err in (errcode)

where errcode is an array.
That syntax doesn't work... is there any alternative syntax to keep
stuff short and not error prone?

I could serialise the array and build up the string of the
where err in (......)
but it doesn't make things look that better.


thx

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Ivan Sergio Borgonovo wrote:
> I'm still investigating on how to return array elements.
>
> I came out with something like:
>
> create or replace function auz(out _errcode int, out _errmsg text)
> returns setof record as $$
> declare
>     errcode int[];
>     errmsg text[];
>     _row record;
> begin
>     errcode[1]:=1;
>     errmsg[1]:='pota pota';
>     errcode[2]:=3;
>     errmsg[2]:='bau bau';
>     for i IN coalesce(array_lower(errcode,1),0) ..
>            coalesce(array_upper(errcode,1),-1)
>          loop _errcode:=errcode[i];
>         _errmsg:=errmsg[i]; return next;
>     end loop;
>     return;
> end;
> $$ language plpgsql;
>
> I'd like to avoid to hard code error messages in the function so I
> was thinking about adding a table with error_id, error_messages and
> change the for loop with something similar to
>
> for _row in
>     select err, msg from errortable where err in (errcode)
>
> where errcode is an array.
> That syntax doesn't work... is there any alternative syntax to keep
> stuff short and not error prone?
>

How about something like:

select err, msg from errortable
where err in (array_to_string(errcode, ','))

b

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.

            regards, tom lane

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