CODEWITHSUNDEEP
postgresqlif-statementselectplpgsql
Jscore
Jscore

Reputation: 405

How to run SELECT queries in PL/pgSQL IF statements

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$

However I get the following error, ERROR: cannot use RETURN QUERY in a non-SETOF function.

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

UPDATE: This ended up working for me:

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

Upvotes: 3

Views: 8660

Answers (2)

klin
klin

Reputation: 121594

From the documentation:

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

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

Note the proper use of ELSIF.

Upvotes: 3

Jim Jones
Jim Jones

Reputation: 19613

I don't think anonymous code blocks support it. Try creating a function and defining its resultset to table, e.g:

CREATE OR REPLACE FUNCTION myfunc() RETURNS TABLE (val INT) AS $$
BEGIN
  RETURN QUERY SELECT 1;
END;
$$ LANGUAGE plpgsql;

To call your function you could use:

SELECT * FROM myfunc();

Note: keep in mind that the table declared on the function's header needs to have the same fields returned in the RETURN QUERY statement.

Upvotes: 2

Related Questions