Thread: Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause [re-post]
Good morning, I have a function argument blah of type text containing something like 33,44,55,66 . Can I cast it in some way to use it in an IN clause as integers like UPDATE foo SET x = y WHERE id IN ( blah ); or need I revert to dynamic SQL (EXECUTE...) ? Thanks, Axel --- axel.rau@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos claudius
On Mon, Oct 25, 2010 at 9:07 AM, Axel Rau <Axel.Rau@chaos1.de> wrote: > I have a function argument blah of type text containing something like > 33,44,55,66 > . Can I cast it in some way to use it in an IN clause as integers like > UPDATE foo SET x = y WHERE id IN ( blah ); Here is what I think should work: UPDATE foo Set x = y WHERE id = ANY( CAST( string_to_array( '1,2,3,4', ',' ) AS INTEGER[] )); -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Hi, You can do this: UPDATE foo SET x = y WHERE id = ANY(string_to_array(blah, ',')::integer[]); Note that you need to cast string_to_array(...) to array type of your id type. On 25 October 2010 20:07, Axel Rau <Axel.Rau@chaos1.de> wrote: > Good morning, > > I have a function argument blah of type text containing something like > 33,44,55,66 > . Can I cast it in some way to use it in an IN clause as integers like > UPDATE foo SET x = y WHERE id IN ( blah ); > or need I revert to dynamic SQL (EXECUTE...) ? > > Thanks, Axel > --- > axel.rau@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos > claudius > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
Thanks Richard and Sergey, your solution works perfect, even if blah contains only one member. Am 25.10.2010 um 18:17 schrieb Richard Broersma: > > Here is what I think should work: > > UPDATE foo > Set x = y > WHERE id = ANY( CAST( string_to_array( '1,2,3,4', ',' ) AS > INTEGER[] )); Axel --- axel.rau@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos claudius