Horton

Thread

  1. Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause [re-post]

    Axel Rau <axel.rau@chaos1.de> — 2010-10-25T16:07:51Z

    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

  2. Re: Using PL/pgSQL text argument in 'IN (INT, INT, ...)' clause [re-post]

    Richard Broersma <richard.broersma@gmail.com> — 2010-10-25T16:17:01Z

    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

  3. Re: Using PL/pgSQL text argument in 'IN (INT, INT, ...)' clause [re-post]

    Sergey Konoplev <gray.ru@gmail.com> — 2010-10-25T16:17:55Z

    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

  4. Re: Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause [re-post]

    Axel Rau <axel.rau@chaos1.de> — 2010-10-25T19:01:40Z

    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