问题:如何在 PL/pgSQL IF 语句中运行 SELECT 查询

我正在尝试使用以下代码在 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$

但是我收到以下错误,ERROR: cannot use RETURN QUERY in a non-SETOF function。

有谁知道我怎样才能让上面的代码工作?谢谢你。

**更新:**这最终为我工作:

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);

解答

来自的文档:

代码块被视为没有参数的函数体,返回 void。

您只能在返回SETOF <type>或TABLE(...)的函数中使用RETURN QUERY。使用表"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。