agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: Results list String to comma separated int
Date: Wed, 4 Dec 2013 19:02:07 -0800 (PST)
Message-ID: <1386212527214-5781774.post@n5.nabble.com> (raw)
In-Reply-To: <1386177104691-5781666.post@n5.nabble.com>
References: <1386177104691-5781666.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
gilsonk wrote
> I have the following situation:
/
> SELECT * FROM table_a WHERE id in (SELECT list_ids FROM table_b WHERE
> id_table_b = 1234);
/
>
> The problem that the ID of 'table_a' is int and result of 'list_ids' is
> string (character varying)
> Example return of table_b: ("1234,1235,1236,1237").
/
> SELECT * FROM WHERE id in ("1234,1235,1236,1237");
/
> Error = cast.
> I need convert to (1234,1235,1236,1237);
>
> I have used "unnest(string_to_array())" and to_char(list_ids,'9999'), no
> sucess.
>
> To not break the list_ids and search for a FOR or WHILE (FUNCTION)
> one-to-ono, there is a solution?!
> Note: I'm using it in a function and where the Sub SELECT is from a
> variable;
> Thanks for help.
What you want to do is convert the text into an ARRAY:
http://www.postgresql.org/docs/9.2/interactive/functions-string.html
specifically: regexp_split_to_array (with possible casting of the resultant
array)
alternative: string_to_array (which you indicated you've seen)
and then use array comparison constructs:
http://www.postgresql.org/docs/9.3/interactive/functions-array.html
specifically: " = ANY (array) "
Combine the two:
SELECT * FROM generate_series(1, 10) gs (s)
WHERE s = ANY (string_to_array('1,2,3' , ',')::integer[])
The "unnest(string_to_array())" mechanic can be made to work as well:
SELECT * FROM generate_series(1, 10) gs (s)
WHERE s IN ( SELECT unnest (string_to_array('1,2,3' , ',')::integer[]) )
The big thing in both examples is casting the resultant "text[]" to
"integer[]" so the types match - in this case at least. The specific
casting, if any, is determined by your data. This approach is fairly generic
in nature.
In a function you'd just write
WHERE id = ANY( string_to_array(text_input_var_name, ',')::integer[] )
I like this much better than explicitly unnesting the array and using IN.
The only time you need to unnest is if you want to apply a filter to the
array before performing the lookup.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Results-list-String-to-comma-separated-int-tp5781666p5781774...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Message-ID: <1386212527214-5781774.post@n5.nabble.com>
Permalink: ../1386212527214-5781774.post@n5.nabble.com/
Also on: postgresql.org/message-id/1386212527214-5781774.post@n5.nabble.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com
Subject: Re: Results list String to comma separated int
In-Reply-To: <1386212527214-5781774.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox