postgresql - 如何在 PL/pgSQL IF 语句中运行 SELECT 查询

Question

我正在尝试使用以下代码在 PL/pgSQL IF 语句中运行 SELECT 查询:

DO
$do$
DECLARE
    query_type   real;
    arr real[] := array[1];
BEGIN
    IF query_type = 1 THEN
        RETURN QUERY
        SELECT "Westminster".*
        FROM "Westminster"
        WHERE ("Westminster".intersects = false AND "Westminster".area <= 100);
    ELSE IF query_type = 0 THEN 
        RETURN QUERY
        SELECT "Westminster".*
        FROM "Westminster";
    END IF;
END
$do$

但是我收到以下错误，错误:无法在非 SETOF 函数中使用 RETURN QUERY。

有谁知道如何让上面的代码工作？谢谢。

更新:这最终对我有用:

CREATE OR REPLACE FUNCTION my_function(query_type integer)
RETURNS SETOF "Westminster" LANGUAGE plpgsql as $$
BEGIN
    IF query_type = 1 THEN
        RETURN QUERY
        SELECT "Westminster".*
        FROM "Westminster"
        WHERE ("Westminster".intersects = false AND "Westminster".area <= 100);
    ELSIF query_type = 0 THEN 
        RETURN QUERY
        SELECT "Westminster".*
        FROM "Westminster";
    END IF;
END;
$$;

然后我这样调用函数:

SELECT * FROM my_function(1);

Answer 1

来自 the documentation:

The code block is treated as though it were the body of a function with no parameters, returning void.

您可以使用 RETURN QUERY仅在返回 SETOF <type> 的函数中或 TABLE(...) .使用表格 "Westminster"作为结果类型，例如:

CREATE OR REPLACE FUNCTION my_function(query_type int)
RETURNS SETOF "Westminster" LANGUAGE plpgsql as $$
BEGIN
    IF query_type = 1 THEN
        RETURN QUERY
        SELECT "Westminster".*
        FROM "Westminster"
        WHERE ("Westminster".intersects = false AND "Westminster".area <= 100);
    ELSIF query_type = 0 THEN 
        RETURN QUERY
        SELECT "Westminster".*
        FROM "Westminster";
    END IF;
END;
$$;

-- exemplary use:

SELECT * FROM my_function(1); 

注意正确使用ELSIF .