带有正则表达式的案例："set-valued function called in context that cannot accept a set"

Question

我正在尝试在PostgreSQL 9.0.1中进行一个稍微复杂的字符串转换。my_col中的值是长字符串，格式为：

'12345_sometext_X12B_1'
'12345_sometext_optionaltext_Y09B_1'
'12345_sometext_optionaltext_X12A_1'

我需要将"X12"部分转换为已知数值，有几个不同的已知值(最多5个)。

我希望能够在一个查询中确定这一点，而不需要子查询。然而，以下内容对我不起作用。最后一列抛出了异常。由于某种原因，我似乎无法使用这些函数的输出组合来执行CASE语句。我把前面的专栏包括在内只是为了示范。

select
regexp_matches(my_col, E'^.*_([^_]*)[A-Z]{1}_\d*$'), -- returns {'X12'}
(regexp_matches(my_col, E'^.*_([^_]*)[A-Z]{1}_\d*$'))[1], -- returns 'X12'
case (regexp_matches(my_col, E'^.*_([^_]*)[A-Z]{1}_\d*$'))[1]
when 'X12' then '1200'
when 'Y09' then '950'
else '?' end -- should return '1200' but throws error
from my_table;

相反，我得到了错误：

ERROR: set-valued function called in context that cannot accept a set
SQL state: 0A000

有人能给我建议吗？

Answer 1

给定数据：

create table my_table(my_col text);
insert into my_table(my_col) values
('12345_sometext_X12B_1'),
('12345_sometext_optionaltext_Y09B_1'),
('12345_sometext_optionaltext_X12A_1'),
('nomatch');

上面的查询确实会产生您报告的错误。非常奇怪，因为：

SELECT pg_typeof((regexp_matches(my_col, E'^.*_([^_]*)[A-Z]{1}_\d*$'))[1]);

返回"text"。实际上应该是setof text，这就是陷阱：regex_matches是一个集返回函数。那些。。。有趣的在PostgreSQL的FROM子句之外调用时的行为。

来自模式匹配：

regexp_matches函数返回所有通过匹配POSIX正则表达式而捕获的子字符串图案它的语法为regexp_matches(string，pattern[，flags])。该函数可以不返回任何行、一行或多行

尝试重新制定查询以使用子查询来调用SRF。如果匹配器返回一行以上，则此操作将失败，不过：

SELECT 
CASE (SELECT x[1] FROM regexp_matches(my_col, E'^.*_([^_]*)[A-Z]{1}_\d*$') x)
WHEN 'X12' THEN '1200'
WHEN 'Y09' THEN '950'
ELSE '?'
END
FROM my_table;

---

想看看SELECT中的SRF在Pg中有多奇怪吗？比较这些查询的结果：

SELECT generate_series(1,10), generate_series(1,15);

和：

SELECT generate_series(1,10), generate_series(1,20);

第一行产生30行。第二个产生20。解释原因很有趣。Pg中的SELECT列表中有多个SRF会产生疯狂但偶尔有用的结果。

PostgreSQL 9.3支持SQL标准LATERAL子句，这要归功于Tom Lane，它为当前行为提供了一个合理且定义良好的替代方案。

Answer 2

regexp_matches()返回SETOF text[](文本数组的集合)，这对于同一字符串中一个模式的多个匹配非常有用。但这只是该任务的错误工具。

请将substring()与正则表达式一起使用。返回text。重复使用@Craig的答案中的演示表：

SELECT CASE substring(my_col, '^.*_([^_]*)[A-Z]_d*$')
WHEN 'X12' THEN '1200'
WHEN 'Y09' THEN '950'
ELSE            '?'
END As result
FROM   my_table;

退货：

result
--------
1200
950
1200
?

还稍微简化了正则表达式。CCD_ 10只是噪声。

如果需要优化性能，请尝试不使用正则表达式，因为正则表达式功能强大，但相对昂贵。类似于：

reverse(right(split_part(reverse(my_col), '_', 2), -1))

看起来更复杂，但在我的测试中仍然更快。