在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

我在寻找一个像regexp_split_to_table这样的函数，但是我们的db是8.2.9版本，所以它没有。我只是在空间上分裂，就像弦一样

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?

是否有一个简单的函数可以处理这个问题，或者我必须自己写一些东西?

2 个解决方案

#1

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:

可以使用unnest函数将数组拆分为resultset，还可以使用string_to_array函数将字符串文本转换为数组。两者结合，你会得到:

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:

因为8.2没有UNNEST，所以可以用PostgreSQL这样写:

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;
$$;

#2

I think you'll have to RETURNS SET or RETURNS TABLE yourself.

我想你必须自己返回SET或return TABLE。

Updated answer: using PL/pgSQL:

答:更新使用PL / pgSQL:

pg=> CREATE OR REPLACE FUNCTION string_to_rows(text) RETURNS SETOF TEXT AS $$ 
  DECLARE
    elems text[];      
  BEGIN
    elems := string_to_array($1, ' ');
    FOR i IN array_lower(elems, 1) .. array_upper(elems, 1) LOOP
      RETURN NEXT elems[i];
    END LOOP;
    RETURN;
  END
$$ LANGUAGE 'plpgsql';
CREATE FUNCTION

pg=> SELECT "Column" FROM string_to_rows('how now brown cow') d("Column");
 Column 
--------
 how
 now
 brown
 cow
(4 rows)

Original answer: using PL/perl:

使用PL / perl原始回答::

pg=> CREATE LANGUAGE plperl; 
CREATE LANGUAGE

pg=> 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 FUNCTION

pg=> SELECT "Column" FROM psplit_to_rows('how now brown cow') d("Column");
 Column 
--------
 how
 now
 brown
 cow
(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 *, but, there you are.)

显然，您可以扩展它来处理您所选择的分隔符等(注意，我不确定您是否真的想要那个名为“column”的列，需要标识符引用以避免关键字冲突，但是，您确实需要)。

#1