在PostgreSQL中将字符串按空格分割成表的SQL语句

Question

我正在寻找类似于regexp_split_to_table的函数，但是我们的数据库版本是8.2.9，所以没有这个函数。我实际上只是在使用空格进行分割，因此像

how now brown cow

这样的字符串将返回

+------+
|Column|
+------+
|how   | 
|now   | 
|brown | 
|cow   |
+------+

有没有一个简单的函数可以处理这个问题，或者我需要自己编写一个函数？

- veilig

---

regexp_split_to_table是在8.3版本中引入的，所以你差一点就拥有这个功能了 :- ( - Randall

Answer 1

通过使用unnest函数，您可以将数组拆分为结果集；通过使用string_to_array函数，您可以将字符串文字转换为数组。将两者结合起来，您可以得到以下内容：

alvherre=# select unnest(string_to_array('the quick lazy fox', ' '));
 unnest 
--------
 the
 quick
 lazy
 fox
(4 filas)

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

- alvherre

Answer 2

我认为你需要自己使用RETURNS SET或RETURNS TABLE。

更新的回答：使用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:

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)

显然，您可以将此扩展以处理您选择的分隔符等。 （请注意，我不确定您是否真的想要将该列命名为“Column”，需要使用标识符引用来避免关键字冲突，但是，您在那里。）

- pilcrow

---

我没有可用的plperl，是否可以使用plpgsql轻松完成此操作？ - veilig

@pilcrow，你的函数很好用，但是当我尝试执行像[select string_to_rows(column) from table]这样的查询时，我遇到了问题。它一直报错： ERROR: set-valued function called in context that cannot accept a set.在网上查找后，我发现需要像这样调用[select * from string_to_rows(text)]。是否可以调用此函数并传入一个集合？ 例如[select * from string_to_rows(select col from table)]。 - veilig

@veilig，您正在尝试将一个集合或表格放入SQL标量上下文中。我认为适当的做法是提出另一个问题，向我们展示您的初始数据和所需结果集。 - pilcrow