Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Syntax of PL/pgSQL
Function parameters
21. VARIADIC parameters
Handling exceptions
Summary

Instruction

Postgres functions can also accept a varying number of arguments.

The arguments must each have the same data type, and they are passed to the function as an array. The parameter has to be marked as VARIADIC. See the example below:

CREATE FUNCTION get_total_dep_employees(VARIADIC dep_list integer[])
RETURNS integer AS $$
DECLARE
  i integer;
  dep_id integer;
  total integer := 0;
  dep_emps integer;
BEGIN
  FOR i in 1 .. array_upper(dep_list, 1) LOOP
    dep_id = dep_list[i];
    dep_emps := (
      SELECT COUNT(*)
      FROM person
      WHERE department_id = i
    );
    total := total + dep_emps;
  END LOOP;
  RETURN total;
END;
$$ LANGUAGE plpgsql;

SELECT
  get_total_dep_employees(1),
  get_total_dep_employees(1, 2),
  get_total_dep_employees(1, 2, 3);

To get the length of the array, you may use the following expression: array_upper(dep_list, 1).

Note: Postgres uses 1-based numbering for array indices, so the first array element has an index of 1 and not 0: dep_list[1].

Exercise

Create a new function named get_formatted_departments() that has one VARIADIC parameter named dep_list. This parameter basically represents a list of department names and has a type of text[].

The function will return formatted TEXT that contains concatenated departments, separated by a vertical bar, '|'. If one of the department names is NULL, you should replace the name with 'Unknown'.

Here is an example of invoking the function:

SELECT get_formatted_departments('HR', NULL, 'Staffing');

The following text should be returned:

HR|Unknown|Staffing

Stuck? Here's a hint!

To format the list of departments, you may write the FOR ... LOOP with the COALESCE() and SUBSTR() functions:

DECLARE
  i integer;
  t text := '';
BEGIN
  FOR i in 1 .. array_upper(dep_list, 1) LOOP
    t := CONCAT(t, COALESCE(dep_list[i], 'Unknown'), '|');
  END LOOP;
  t := SUBSTR(t, 1, LENGTH(t) - 1);
  RETURN t;
END;

Or you may use the following function:

array_to_string(dep_list, '|', 'Unknown')

The first argument is the array, the second is the character that should separate the array elements in the concatenated string, and the third is the value that should replace any NULLs.