I looking for a function like regexp_split_to_table, but our db is version 8.2.9, so it doesn't have it. I'm really only splitting on a space, so a string like
how now brown cow
would return
+------+|Column|+------+|how | |now | |brown | |cow |+------+is there a simple function that can handle this, or something I have to write myself?
Best Answer
You can split an array to a resultset by using the unnest function, and you can turn a string literal into an array by using the string_to_array function. Combine both and you get this:
alvherre=# select unnest(string_to_array('the quick lazy fox', ' '));unnest --------thequicklazyfox(4 filas)Since 8.2 does not have UNNEST, you can write it in PostgreSQL like this:
create or replace function unnest(anyarray) returns setof anyelementlanguage sql as $$select $1[i] from generate_series(array_lower($1, 1),array_upper($1, 1)) as i;$$; I think you'll have to RETURNS SET or RETURNS TABLE yourself.
Updated answer: using PL/pgSQL:
pg=> CREATE OR REPLACE FUNCTION string_to_rows(text) RETURNS SETOF TEXT AS $$ DECLAREelems text[]; BEGINelems := string_to_array($1, ' ');FOR i IN array_lower(elems, 1) .. array_upper(elems, 1) LOOPRETURN NEXT elems[i];END LOOP;RETURN;END$$ LANGUAGE 'plpgsql';CREATE FUNCTIONpg=> SELECT "Column" FROM string_to_rows('how now brown cow') d("Column");Column --------hownowbrowncow(4 rows)Original answer: using PL/perl:
pg=> CREATE LANGUAGE plperl; CREATE LANGUAGEpg=> CREATE FUNCTION psplit_to_rows(text) RETURNS SETOF TEXT AS $$pg$> for my $t (split ' ', $_[0]) { return_next $t; }pg$> undef;pg$> $$ LANGUAGE plperl;CREATE FUNCTIONpg=> SELECT "Column" FROM psplit_to_rows('how now brown cow') d("Column");Column --------hownowbrowncow(4 rows)Obviously you can extend this to handle a delimiter of your choosing, etc. (Note, I'm not sure if you really wanted that column named "Column", requiring identifier quoting to avoid keyword clash, but, there you are.)