Re: PgSQL problem: How to split strings into rows

Kynn Jones <kynnjo(at)gmail(dot)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°