Functions with variable number of input parameters

by

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.

  • 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:

  1. Positional notation
    Is the simple way, but it only allows to omit the rightmost parameter(s):

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

  2. Named notation
    Allows to omit any parameter that has a default value:

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

  3. 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.

More Related Contents:

  1. Difference between language sql and language plpgsql in PostgreSQL functions
  2. How do I (or can I) SELECT DISTINCT on multiple columns?
  3. Pass multiple values in single parameter
  4. updating table rows in postgres using subquery
  5. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  6. Return pre-UPDATE column values using SQL only
  7. What is the difference between function and procedure in PL/SQL?
  8. Casting NULL type when updating multiple rows
  9. Slow simple update query on PostgreSQL database with 3 million rows
  10. Return setof record (virtual table) from function
  11. SQL update fields of one table from fields of another one
  12. Solution to “cannot perform a DML operation inside a query”?
  13. Bulk/batch update/upsert in PostgreSQL
  14. Postgres error updating column data
  15. Update a column of a table with a column of another table in PostgreSQL
  16. Functions vs Stored Procedures
  17. Paging with Oracle
  18. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  19. Optimize query with OFFSET on large table
  20. How to concatenate columns in a Postgres SELECT?
  21. How to use (install) dblink in PostgreSQL?
  22. PostgreSQL multi INSERT…RETURNING with multiple columns
  23. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  24. In SQL Server, what does “SET ANSI_NULLS ON” mean?
  25. Grant privileges for a particular database in PostgreSQL
  26. How to get Return Value of a Stored Procedure
  27. Check if NULL exists in Postgres array
  28. Dynamic order direction
  29. Return rows from INSERT with ON CONFLICT without needing to update
  30. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess

Leave a Comment