Functions with a variable number of input parameters

Question

I create a stored procedure (function) in a PostgreSQL database that updates the table depending on its input. To create a variable number of a parameter function, I create an additional input parameter called a mode, which I use to control the parameters that I use in the update request.

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 better? To create an update_site(<all the columns of table>) function update_site(<all the columns of table>) and a separate update_site(id integer, <varchar column to update>) function update_site(id integer, <varchar column to update>) , or use the mode in one function to determine the difference? Which option is more effective? One unique feature or different for each purpose?

Answer 1

Advanced features like VARIADIC or even polymorphic input types and dynamic SQL are very powerful. The final chapter of this answer contains an extended example:

• Refactoring the PL / pgSQL function to return the results of various SELECT queries

But for a simple case like yours, you can just use the default values for the function parameters. It all depends on the exact requirements.
If all the columns in question are defined as NOT NULL , this will probably be simpler and faster:

 CREATE OR REPLACE FUNCTION update_site( _name text -- always required, so no default , _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; 

Be sure to read about the default values ​​in the manual !

To avoid name conflicts between parameters and column names, I make it a habit to add the input parameter prefix to _ . It is a matter of taste and style.

• The first parameter name does not have a default value, as it is required at all times.
• Other parameters may be omitted.
• At least one is required, or WARNING occurs and nothing else happens.
• UPDATE will only change the columns for the given parameters.
• It can be easily expanded for N parameters.

Function call

For Postgres 9.5 or later:

A simple way is with positional notation for parameters. This only allows you to omit the rightmost parameter (s):

 SELECT update_site('foo', 'New York'); -- no telephone 

Named notation allows you to omit any parameter that has a default value:

 SELECT update_site(name => 'foo', _telephone => 123); -- no city 

Both can be combined in a mixed record :

 SELECT update_site('foo', _telephone => 123); -- still no city 

Prior to Postgres 9.4 or later , := used for assignment in a call:

 SELECT update_site(name := 'foo', _telephone := 123); SELECT update_site('foo', _telephone := 123); 

Still valid in Postgres 11 for backward compatibility, but do not use it in current versions.

Answer 2

There are a few things you want to learn:

• Dynamically building SQL using the format function and its %I and %L qualifiers, then executing it using EXECUTE ... USING ; and

• Using VARIADIC parameters to accept variable numbers of function arguments, with the caveat that they should all be the same data types.