Обсуждение: Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause
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
Hey Axel,<br /><br />How about this solution:<br />UPDATE foo SET x = y WHERE ANY(string_to_array(blah, ',')) = id;<br />?<br/><br /><div class="gmail_quote">2010/10/25 Axel Rau <span dir="ltr"><<a href="mailto:Axel.Rau@chaos1.de">Axel.Rau@chaos1.de</a>></span><br/><blockquote class="gmail_quote" style="margin: 0pt0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">Good morning,<br /><br /> I have a functionargument blah of type text containing something like<br /> 33,44,55,66<br /> . Can I cast it in some way to useit in an IN clause as integers like<br /> UPDATE foo SET x = y WHERE id IN ( blah );<br /> or need I revert to dynamicSQL (EXECUTE...) ?<br /><br /> Thanks, Axel<br /> ---<br /><a href="mailto:axel.rau@chaos1.de" target="_blank">axel.rau@chaos1.de</a> PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos claudius<br /><font color="#888888"><br/><br /> -- <br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org" target="_blank">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br /></font></blockquote></div><br/><br clear="all" /><br />-- <br />// Dmitriy.<br /><br /><br />
Ooops, sorry
UPDATE foo SET x = y WHERE id = ANY(string_to_array(blah, ',')::integer[]);
--
// Dmitriy.
UPDATE foo SET x = y WHERE id = ANY(string_to_array(blah, ',')::integer[]);
2010/10/29 Dmitriy Igrishin <dmitigr@gmail.com>
Hey Axel,
How about this solution:
UPDATE foo SET x = y WHERE ANY(string_to_array(blah, ',')) = id;
?2010/10/25 Axel Rau <Axel.Rau@chaos1.de>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
--
// Dmitriy.
--
// Dmitriy.