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")
CodePudding user response:
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$;