imploding/using arrays for IN (...)
Passing an array to a PL/pgSQL function, is it
possible to implode it and use with IN (...) ? For
example:
my_func(int[])
ids := implode_func($1);
select *
from x
where id in (ids);
I tried concatenating ids together with ',', but the
query gives an integer/text cast error. Or, is it
possible use arrays in IN()?
select *
from x
where id in ({1,2,3});
__________________________________________________
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com
One way you can achieve what I think you are after without using an array is
select * from x where $1 like '%,'||id||',%';
Where $1 will be a string with the example value of ',1,2,3,';
for the table x with the rows having IDs of 1, 2, 3, 4 and 5, the above
query will return rows 1, 2 and 3.
This is a tricky idea to wrap your head around the first time you see it,
but do some examples on paper and see for yourself how it works.
----- Original Message -----
From: "Cool Screen" <cool_screen_name90001@yahoo.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, October 18, 2002 8:11 AM
Subject: [GENERAL] imploding/using arrays for IN (...)
Show quoted text
Passing an array to a PL/pgSQL function, is it
possible to implode it and use with IN (...) ? For
example:my_func(int[])
ids := implode_func($1);select *
from x
where id in (ids);I tried concatenating ids together with ',', but the
query gives an integer/text cast error. Or, is it
possible use arrays in IN()?select *
from x
where id in ({1,2,3});__________________________________________________
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Thu, 17 Oct 2002, Cool Screen wrote:
Passing an array to a PL/pgSQL function, is it
possible to implode it and use with IN (...) ? For
example:my_func(int[])
ids := implode_func($1);select *
from x
where id in (ids);I tried concatenating ids together with ',', but the
query gives an integer/text cast error. Or, is it
possible use arrays in IN()?
Not really, but you may want to look at contrib/array
for some functions/operators that do item in array
lookups.