shortcut for select * where id in (arrayvar)

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: <pgsql-general(at)postgresql(dot)org>
Subject: shortcut for select * where id in (arrayvar)
Date: 2008-03-30 21:56:01
Message-ID: 20080330235601.3a286963@webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joris Dobbelsteen 2008-03-30 21:59:36 Re: database 1.2G, pg_dump 73M?!
Previous Message Ross Boylan 2008-03-30 21:43:26 Re: database 1.2G, pg_dump 73M?!