postgresql 从 regexp_matches 中检索结果作为一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16609554/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieve results from regexp_matches as one row
提问by Oto Shavadze
So, if have this string aassdd
所以，如果有这个字符串 aassdd
This code:
这段代码：
regexp_matches('aassdd', 'a', 'g')
regexp_matches('aassdd', 'a', 'g')
returns 2 different rows.
返回 2 个不同的行。
It is possible to retrieve all matches as one row? for example as arraytype as one row, that is from code above, needed result is: {a,a}
可以将所有匹配项检索为一行吗？例如作为array一行的类型，即来自上面的代码，需要的结果是：{a,a}
采纳答案by a_horse_with_no_name
The fact that regexp_matches() returns a set rather than a scalar is understandable but still somewhat annoying.
regexp_matches() 返回一个集合而不是一个标量的事实是可以理解的，但仍然有些烦人。
The only workaround I found is this somewhat ugly query:
我发现的唯一解决方法是这个有点难看的查询：
select array_agg(i)
from (
select (regexp_matches('aassdd', 'a', 'g'))[1] i
) t
回答by Muhammad Usama
SELECT ARRAY(select array_to_string(regexp_matches('aassdd', 'a', 'g'),''));
回答by RusArt
If regexp_matches() takes as parameter a column of an indexed table, there is one more way:
如果 regexp_matches() 将索引表的列作为参数，还有另一种方法：
SELECT rid, array_agg(number)
FROM
(SELECT
rid,
(regexp_matches(column,'[0-9]+','g'))[1] as number
FROM table) t
GROUP BY rid
回答by Evan Carroll
With plperl
和 plperl
If you're looking for something somewhat performant,
如果您正在寻找性能稍高的东西，
CREATE LANGUAGE plperl;
CREATE FUNCTION regexp_return_matches(text, text)
RETURNS text[]
AS $$
my ( $input, $pattern ) = @_;
$pattern = quotemeta($pattern);
return [$input =~ m/($pattern)/g];
$$ LANGUAGE plperl;
SELECT regexp_return_matches('aassdd', 'a');
regexp_return_matches
-----------------------
{a,a}
(1 row)
