Re: Results list String to comma separated int

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.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.