Optional argument in PL/pgSQL function
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 asRETURNS SETOF foobar
instead ofRETURNS 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 theIF
expression and theWHERE
clause accordingly.IF
statements are not available in plain SQL. Has to beLANGUAGE 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$;
Elaborating on Frank's answer on this thread:
The VARIADIC
agument doesn't have to be the only argument, only the last one.
You can use VARIADIC
for functions that may take zero variadic arguments, it's just a little fiddlier in that it requires a different calling style for zero args. You can provide a wrapper function to hide the ugliness. Given an initial varardic function definition like:
CREATE OR REPLACE FUNCTION foofunc(param1 integer, param2 date, param2 date, optional_list_of_ids VARIADIC integer[]) RETURNS SETOF RECORD AS $$....$$ language sql;
For zero args use a wrapper like:
CREATE OR REPLACE FUNCTION foofunc(integer, date, date) RETURNS SETOF RECORD AS $body$SELECT foofunc($1,$2,$3,VARIADIC ARRAY[]::integer[]);$body$ LANGUAGE 'sql';
or just call the main func with an empty array like VARIADIC '{}'::integer[]
directly. The wrapper is ugly, but it's contained ugliness, so I'd recommend using a wrapper.
Direct calls can be made in variadic form:
SELECT foofunc(1,'2011-01-01','2011-01-01', 1, 2, 3, 4);
... or array call form with array ctor:
SELECT foofunc(1,'2011-01-01','2011-01-01', VARIADIC ARRAY[1,2,3,4]);
... or array text literal form:
SELECT foofunc(1,'2011-01-01','2011-01-01', VARIADIC '{1,2,3,4}'::int[]);
The latter two forms work with empty arrays.