PosgreSQL function passing parameters by name

user_6654292 0
Viewed 35

Is it possible to create a function like this:

CREATE FUNCTION Testing(p_1 INTEGER DEFAULT NULL, p_2 INTEGER DEFAULT NULL, p_3 DATE DEFAULT current_date)
    RETURNS TABLE
    (
        column_a INTEGER,
        column_b INTEGER,
        column_c INTEGER,
        column_d DATE
    )
LANGUAGE plpgsql
AS 
$f$
    BEGIN
        RETURN QUERY
        SELECT column_a, column_b, column_c, column_d
        FROM table_test
        WHERE column_d <= p_3
        AND CASE WHEN NOT p_1 IS NULL THEN column_a = p_1 ELSE TRUE END
        AND CASE WHEN NOT p_2 IS NULL THEN column_b = p_2 ELSE TRUE END;
    END;
$f$;

And then call it specifying the parameter name, like:

SELECT * FROM Testing(p_2 = 23)

(If I try that, for instance, PostgreSQL returns "column "p_2" does not exist")

plpgsql parameters arguments postgresql function
1 Answers

Accordnig to the documentation:

In named notation, each argument's name is specified using => to separate it from the argument expression.

So the correct way is:

SELECT * FROM Testing(p_2 => 23)

The function however is not perfect. Column names in the returning table are the same as those used in the query, so they are ambiguous. Use an alias in the query:

CREATE FUNCTION Testing(p_1 INTEGER DEFAULT NULL, p_2 INTEGER DEFAULT NULL, p_3 DATE DEFAULT current_date)
    RETURNS TABLE
    (
        column_a INTEGER,
        column_b INTEGER,
        column_c INTEGER,
        column_d DATE
    )
LANGUAGE plpgsql
AS 
$f$
    BEGIN
        RETURN QUERY
        SELECT t.column_a, t.column_b, t.column_c, t.column_d
        FROM table_test t
        WHERE t.column_d <= p_3
        AND CASE WHEN NOT p_1 IS NULL THEN t.column_a = p_1 ELSE TRUE END
        AND CASE WHEN NOT p_2 IS NULL THEN t.column_b = p_2 ELSE TRUE END;
    END;
$f$;

Alternatively, use SETOF if the table has only four columns:

CREATE FUNCTION Testing(p_1 INTEGER DEFAULT NULL, p_2 INTEGER DEFAULT NULL, p_3 DATE DEFAULT current_date)
    RETURNS SETOF table_test
    LANGUAGE plpgsql
AS 
$f$
    BEGIN
        RETURN QUERY
        SELECT column_a, column_b, column_c, column_d
        FROM table_test
        WHERE column_d <= p_3
        AND CASE WHEN NOT p_1 IS NULL THEN column_a = p_1 ELSE TRUE END
        AND CASE WHEN NOT p_2 IS NULL THEN column_b = p_2 ELSE TRUE END;
    END;
$f$;
user_19957380
Related