Thread: 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 (...)


> 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.