postgresql分割字符串,sql通过空格将字符串拆分为postgresql中的表

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?

解决方案

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

--------

the

quick

lazy

fox

(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 anyelement

language sql as $$

select $1[i] from generate_series(array_lower($1, 1),

array_upper($1, 1)) as i;

$$;