Skip site navigation (1) Skip section navigation (2)

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

From: brian <brian(at)zijn-digital(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: shortcut for select * where id in (arrayvar)
Date: 2008-03-30 23:14:04
Message-ID: 47F01EBC.6020507@zijn-digital.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Dragan ZubacDate: 2008-03-30 23:19:34
Subject: Re: database 1.2G, pg_dump 73M?!
Previous:From: Joris DobbelsteenDate: 2008-03-30 22:44:19
Subject: Re: database 1.2G, pg_dump 73M?!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group