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 function update_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?
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:
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 int DEFAULT NULL)
RETURNS int
LANGUAGE plpgsql AS
$func$
BEGIN
IF _city IS NULL AND _telephone IS NULL THEN
RAISE WARNING 'At least one not-null input value required!';
RETURN; -- nothing to update
END IF;
UPDATE "Sites"
SET "City" = COALESCE(_city, "City")
, "Telephone" = COALESCE(_telephone, "Telephone")
WHERE "SiteName" = _name;
END
$func$;
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.
name
has no default, since it is required at all times.WARNING
is raised and nothing else happens.UPDATE
will only change columns for given parameters.Three options:
Positional notation
Is the simple way, but it 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
Mixed notation
Combines both:
SELECT update_site('foo', _telephone => 123); -- still no city
Up to Postgres 9.4, :=
was used instead of =>
for assignment in the call:
SELECT update_site(name := 'foo', _telephone := 123);
SELECT update_site('foo', _telephone := 123);
Still valid in Postgres 15 for backward compatibility, but rather use modern syntax.