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

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

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: shortcut for select * where id in (arrayvar)
Date: 2008-03-31 07:28:33
Message-ID: 20080331092833.35bb1e1f@webthatworks.it (view raw or flat)
Thread:
Lists: pgsql-general
On Sun, 30 Mar 2008 21:40:52 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ivan Sergio Borgonovo <mail(at)webthatworks(dot)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


In response to

pgsql-general by date

Next:From: Allan KamauDate: 2008-03-31 07:35:47
Subject: Removing redundant itemsets
Previous:From: josep porresDate: 2008-03-31 07:19:20
Subject: Re: PostgreSQL and Java on WindowsXP

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