Functions with variable number of input parameters

Question

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?

Erwin Brandstetter · Accepted Answer · 2023-03-08T11:52:13.520

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:

Refactor a PL/pgSQL function to return the output of various SELECT queries

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.

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

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.

Thanks for your answer. It seems like a really good solution for my question. Do default values can be used on other database programming languages, other than PostgreSQL? — anairinac, May 28 '13 at 19:34
@kzz: These rules apply to PostgreSQL functions only. Other RDBMS have embedded procedural languages, too. Oracle's PL/SQL is the closest, PL/pgSQL was modeled after that. Not sure how PL/SQL or MS tSQL handle default values. The concept of default values is undoubtedly more wide-spread than `VARIADIC` or polymorphic parameters. — Erwin Brandstetter, May 28 '13 at 21:13
I decided to change my accepted answer to this one because its more detailed in answering my question. — anairinac, Jun 03 '13 at 15:37
Nice... I somehow managed to miss the support for default parameters entirely. — Craig Ringer, Jun 04 '13 at 00:29

score 4 · Answer 2 · answered May 28 '13 at 00:30

4

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 with EXECUTE ... USING; and
Using VARIADIC parameters to take variable numbers of arguments to the function, with the caveat that they must all be the same data type.

answered May 28 '13 at 00:30

Craig Ringer

307,061
76
688
778

Thank you for your answer. I found information on EXECUTE format, and can accomplish what I asked for variable parameters, but since EXECUTE is recalculated every time the function is executed, I guess ultimately it would be less efficient than normal functions. I wanted to know if that was possible, but for what I am being asked it is more than needed. Anyways, thanks. I will definitely read more about it, though. Seems interesting. – anairinac May 28 '13 at 02:46
@kzz Yep, it's a useful facilility, but be careful to use `EXECUTE ... USING` or the safe format parameters to avoid accidental SQL injection exposure via in-database dynamic SQL. – Craig Ringer May 28 '13 at 03:21
great answer!!! – ennth Oct 09 '21 at 14:37

Functions with variable number of input parameters

2 Answers2

Function call

Linked