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.
