Re: imploding/using arrays for IN (...)

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" 
To: 
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)
>
>