I am trying to write a PL/pgSQL function with optional arguments. It performs a query based on a filtered set of records (if specified), otherwise performs a query on the entire data set in a table.
For example (PSEUDO CODE):
CREATE OR REPLACE FUNCTION foofunc(param1 integer, param2 date, param2 date, optional_list_of_ids=[]) RETURNS SETOF RECORD AS $$ IF len(optional_list_of_ids) > 0 THEN RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2 AND id in optional_list_of_ids); ELSE RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2); ENDIF $$ LANGUAGE SQL;
What would be the correct way to implement this function?
As an aside, I would like to know how I could call such a function in another outer function. This is how I would do it - is it correct, or is there a better way?
CREATE FUNCTION foofuncwrapper(param1 integer, param2 date, param2 date) RETURNS SETOF RECORD AS $$ BEGIN CREATE TABLE ids AS SELECT id from foobar where id < 100; RETURN QUERY (SELECT * FROM foofunc(param1, param2, ids)); END $$ LANGUAGE SQL
You can assign an optional argument using the assignment operator in a function definition or using the Python **kwargs statement. There are two types of arguments a Python function can accept: positional and optional. Optional arguments are values that do not need to be specified for a function to be called.
Functions with optional arguments offer more flexibility in how you can use them. You can call the function with or without the argument, and if there is no argument in the function call, then a default value is used.
The INOUT modeThe function changes the argument and returns the updated value. The following swap function accepts two integers and their values: create or replace function swap( inout x int, inout y int ) language plpgsql as $$ begin select x,y into y,x; end; $$;
By definition, an Optional Parameter is a handy feature that enables programmers to pass less number of parameters to a function and assign a default value.
Since PostgreSQL 8.4 (which you seem to be running), there are default values for function parameters. If you put your parameter last and provide a default, you can simply omit it from the call:
CREATE OR REPLACE FUNCTION foofunc(_param1 integer , _param2 date , _ids int[] DEFAULT '{}') RETURNS SETOF foobar -- declare return type! LANGUAGE plpgsql AS $func$ BEGIN -- required for plpgsql IF _ids <> '{}'::int[] THEN -- exclude empty array and NULL RETURN QUERY SELECT * FROM foobar WHERE f1 = _param1 AND f2 = _param2 AND id = ANY(_ids); -- "IN" is not proper syntax for arrays ELSE RETURN QUERY SELECT * FROM foobar WHERE f1 = _param1 AND f2 = _param2; END IF; END -- required for plpgsql $func$;
Major points:
The keyword DEFAULT
is used to declare parameter defaults. Short alternative: =
.
I removed the redundant param1
from the messy example.
Since you return SELECT * FROM foobar
, declare the return type as RETURNS SETOF foobar
instead of RETURNS SETOF record
. The latter form with anonymous records is very unwieldy, you'd have to provide a column definition list with every call.
I use an array of integer (int[]
) as function parameter. Adapted the IF
expression and the WHERE
clause accordingly.
IF
statements are not available in plain SQL. Has to be LANGUAGE plpgsql
for that.
Call with or without _ids
:
SELECT * FROM foofunc(1, '2012-1-1'::date);
Effectively the same:
SELECT * FROM foofunc(1, '2012-1-1'::date, '{}'::int[]);
You have to make sure the call is unambiguous. If you have another function of the same name and two parameters, Postgres might not know which to pick. Explicit casting (like I demonstrate) narrows it down. Else, untyped string literals work, too, but being explicit never hurts.
Call from within another function:
CREATE FUNCTION foofuncwrapper(_param1 integer, _param2 date) RETURNS SETOF foobar LANGUAGE plgpsql AS $func$ DECLARE _ids int[] := '{1,2,3}'; BEGIN -- whatever RETURN QUERY SELECT * FROM foofunc(_param1, _param2, _ids); END $func$;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With