在多行情况下使用带有with子句的perform

Question

我试图使用一个返回多行的with查询执行。

CREATE OR REPLACE FUNCTION test_function() RETURNS void AS $$
BEGIN
PERFORM (
WITH selection AS (
    SELECT id,
    ROW_NUMBER() OVER w AS r,
    first_value(id) OVER w AS first_value,
    nth_value(id, 5) OVER w AS last_value
    FROM mytable
    WINDOW w AS (PARTITION BY v.ability_id ORDER BY unit_id ASC)
)
create_question(id, 1, 1, 1) 
FROM selection
WHERE ability_id IN (
    SELECT ability_id
    FROM selection
    WHERE last_value > 0.5
    ORDER BY first_value DESC
)
AND selection.r <= 5
);
END;
$$ LANGUAGE plpgsql;

，我得到错误:

ERROR:  more than one row returned by a subquery used as an expression

postgres文档说不能这样做:

对于WITH查询，使用PERFORM，然后将查询放在括号中。(在本例中，查询只能返回一行。)

除了编写两次With查询(这里称为selection)之外，还可以做些什么来解决这个问题?

Answer 1

备注:您的查询在create_question(id, 1, 1, 1)之前缺少SELECT

技巧是修改查询，使其返回单行。

你可以使用一个聚合函数，例如:

SELECT
   count(create_question(id, 1, 1, 1))
FROM selection
...

那么查询只返回单行，可以在PERFORM语句中作为子查询使用。