Functions with variable number of input parameters
Solution 1
Advanced features like VARIADIC
or even polymorphic input types and dynamic SQL are very powerful. The last chapter in this answer provides an advanced example:
But for a simple case like yours, you can just use default values for function parameters. It all depends on exact requirements.
If the columns in question are all defined NOT NULL
, this would probably be simpler and faster:
CREATE OR REPLACE FUNCTION update_site(_name text -- always required
, _city text DEFAULT NULL
, _telephone integer DEFAULT NULL)
RETURNS integer AS
$func$
BEGIN
IF _city IS NULL AND _telephone IS NULL THEN
RAISE WARNING 'At least one value to update required!';
RETURN; -- nothing to update
END IF;
UPDATE "Sites"
SET "City" = COALESCE(_city, "City")
, "Telephone" = COALESCE(_telephone, "Telephone")
WHERE "SiteName" = _name;
END
$func$ LANGUAGE plpgsql;
Read about default values in the manual!
To avoid naming conflicts between parameters and column names I make it a habit to prefix input parameters with _
. That's a matter of taste and style.
- The first parameter
name
has no default, since it is required at all times. - Other parameters can be omitted.
- At least one is required, or a
WARNING
is raised and nothing else happens. - The
UPDATE
will only change columns for given parameters. - Can easily be expanded for N parameters.
Function call
Since Postgres 9.5:
The simple way is with positional notation for parameters. This only allows to omit the rightmost parameter(s):
SELECT update_site('foo', 'New York'); -- no telephone
Named notation allows to omit any parameter that has a default value:
SELECT update_site(name => 'foo', _telephone => 123); -- no city
Both can be combined in mixed notation:
SELECT update_site('foo', _telephone => 123); -- still no city
In Postgres 9.4 or older, :=
was used for assignment in the call:
SELECT update_site(name := 'foo', _telephone := 123);
SELECT update_site('foo', _telephone := 123);
Still valid in Postgres 12 for backward compatibility, but rather use the modern notation.
Solution 2
There are a few things you'll want to look into:
Dynamically building the SQL using the
format
function and its%I
and%L
specifiers, then executing it withEXECUTE ... USING
; andUsing
VARIADIC
parameters to take variable numbers of arguments to the function, with the caveat that they must all be the same data type.
anairinac
computer science, e-science, artificial intelligence, neural nets, criptography, web design & development
Updated on July 02, 2022Comments
-
anairinac almost 2 years
I'm creating a stored procedure (function) in a PostgreSQL DB, which updates a table depending on its input. In order to create a variable number of parameter function, I'm creating an extra input parameter called mode, which I use to control which parameters I use on the update query.
CREATE OR REPLACE FUNCTION update_site( mode integer, name character varying, city character varying, telephone integer, ) RETURNS integer AS $$ BEGIN IF mode = 0 THEN BEGIN UPDATE "Sites" SET ("City","Telephone") = (city,telephone) WHERE "SiteName" = name; RETURN 1; EXCEPTION WHEN others THEN RAISE NOTICE 'Error on site update: %, %',SQLERRM,SQLSTATE; RETURN 0; END; ELSIF mode = 1 THEN BEGIN UPDATE "Sites" SET "City" = city WHERE "SiteName" = name; RETURN 1; EXCEPTION WHEN others THEN RAISE NOTICE 'Error on site update: %, %',SQLERRM,SQLSTATE; RETURN 0; END; ELSIF mode = 2 THEN BEGIN UPDATE "Sites" SET "Telephone" = telephone WHERE "SiteName" = name; RETURN 1; EXCEPTION WHEN others THEN RAISE NOTICE 'Error on site update: %, %',SQLERRM,SQLSTATE; RETURN 0; END; ELSE RAISE NOTICE 'Error on site update: %, %',SQLERRM,SQLSTATE; RETURN 0; END IF; END; $$ LANGUAGE plpgsql;
What would be best? To create a function
update_site(<all the columns of table>)
and a separate functionupdate_site(id integer, <varchar column to update>)
, or use the mode in one function to define the difference? Which option is more efficient? One unique function or different ones for each purpose?