Search Postgresql Archives

Re: shortcut for select * where id in (arrayvar)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux