Kynn Jones <kynnjo@gmail.com> wrote:
> I have a table X with some column K consisting of whitespace-separated words.
> Is there some SELECT query that will list all these words (for the entire
> table) so that there's one word per row in the returned table? E.g. If the
> table X is
>
> K
> ---------------------
> foo bar baz
> quux frobozz
> eeny meeny
> miny moe
>
> ...I want the result of this query to be
>
> foo
> bar
> baz
> quux
> frobozz
> eeny
> meeny
> miny
> moe
>
> How can I do this? (I have a slight preference for solutions that will work
> with version 8.2, but I'm interested in any solution to the problem.)
With 8.4:
test=*# select string_to_array('foo bar bartz', ' ');
string_to_array
-----------------
{foo,bar,bartz}
(1 Zeile)
Zeit: 23,390 ms
test=*# select unnest(string_to_array('foo bar bartz', ' '));
unnest
--------
foo
bar
bartz
(3 Zeilen)
With 8.2:
You have to create a function unnest:
CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT
LANGUAGE SQL AS $$SELECT $1[i] FROM
generate_series(array_lower($1,1),array_upper($1,1)) i;$$;
string_to_array() should work in 8.2 (i'm not really sure, but i think,
8.2 contains this funtion)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
