Date: Wed, 4 Dec 2013 19:02:07 -0800 (PST)
From: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Message-ID: <1386212527214-5781774.post@n5.nabble.com>
In-Reply-To: <1386177104691-5781666.post@n5.nabble.com>
References: <1386177104691-5781666.post@n5.nabble.com>
Subject: Re: Results list String to comma separated int
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql