如何在PL/pgSQL IF语句中运行SELECT查询 [英] How to run SELECT queries in PL/pgSQL IF statements

问题描述

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

I am trying to run SELECT queries in PL/pgSQL IF statements using the code below:

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.

有人知道我如何获得上述代码吗?谢谢.

Does anyone know how I can get the above code to work? Thank you.

更新:这最终对我有用:

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

然后我像这样调用函数:

I then called the function like this:

SELECT * FROM my_function(1);

推荐答案

来自

将代码块视为没有参数的函数的主体，并返回void.

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

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

You can use RETURN QUERY only in a function returning SETOF <type> or TABLE(...). Use the table "Westminster" as the resulting type, e.g.:

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.

这篇关于如何在PL/pgSQL IF语句中运行SELECT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！