Re: Arrays and ANY problem

>>>>> "David" == David Salisbury <dsalis(at)ucar(dot)edu> writes:

David> I didn't specify the real problem as it's all wrapped up in
David> layers and I didn't want to post a "can someone write the query
David> for me". The real problem was I have a table with a string
David> holding comma separated numbers, and needed to go to a lookup
David> table and replace each of those numbers with it's correlated
David> value. So '12,2,10' gets converted to 'twelve,two,ten'.

David> Tom's "I'd suggest that making his sub-select return a rowset
David> result rather than an array" was spot on and lead me to
David> "unnest". For my posted problem this was the simple solution.
David> Sorry to narrow things down to my specific array method.

David> select name from table_name_ds_tmp where categoryid = ANY ( select
David> unnest(string_to_array( '200,400', ',')::bigint[]) );

This won't necessarily preserve the order of elements (it might
sometimes look like it does, but it's fooling you). It also won't handle
duplicate numbers.

The right solution that does preserve order would be:

select name
from unnest(string_to_array( '200,400', ',')::bigint[])
with ordinality as u(id,ord)
join table_name_ds_tmp t on (t.category_id=u.id)
order by u.ord;

(wrap an ARRAY( ) around that if you need the result as a single array
rather than as rows, or use string_agg(name, ',' order by ord) if you
want a comma-separated string result)

regexp_split_to_table might be a better method than
unnest/string_to_array.

--
Andrew (irc:RhodiumToad)