Passing a variable number of parameters to a function in PostgreSQL

I had a situation where I wanted to pass a variable number of Key/Value pairs to an SQL function, which would then insert values into parent/child pages. After a bit of research, I found that Arrays were the ideal choice.

Take a look at this code:

CREATE OR REPLACE FUNCTION insert_stuff ( in_array TEXT[2][] ) RETURNS void AS $$
BEGIN
  -- Attribute records
  FOR i IN array_lower(in_array, 1)..array_upper(in_array, 1) LOOP
    INSERT INTO stuff (key, value)
      VALUES( in_array[i][1], in_array[i][2]);
  END LOOP;

END;
$$ LANGUAGE plpgsql;

The parameter being passed to the function is in_array TEXT[2][], which means an array of 2-element Text arrays. Here’s some samples of calling the function:

select insert_stuff( ARRAY[['firstname','Fred']] );
select insert_stuff( ARRAY[['firstname','Fred'], ['food','Apple']] );
select insert_stuff( ARRAY[['country','Australia'], ['city','Sydney'], ['weather', 'Cloudy'] );

The result is the ability to pass any number of elements into the function. Yippee!

Concatenate rows with a PostgreSQL aggregate function

Aggregates are a nifty feature of PostgreSQL that let you write your own functions that operate over a number of rows, similar to MIN, MAX, COUNT and AVERAGE.

Basically, a set of rows (typically generated via GROUP BY) are iterated through the Aggregate function, with a final call when the set of rows is complete.

• MIN works by keeping track of the smallest value passed in
• COUNT works by incrementing a counter for each row
• AVERAGE works like SUM and then divides by the equivalent to COUNT

I had a need to concatenate strings from multiple rows into a single string, which I wanted to delimit by blank lines, commas or whatever. For example, let’s say I had some rows:

NAME
----
John
David
Mary
Sally

I wanted to be able to call smoosh(name, ', ') and get the result: John, David, Mary, Sally

The SMOOSH function below does just that:

-- SMOOSH: Combines strings with supplied paramter
-- eg smoosh('fred', 'joe', ', ') ==> 'fred, joe'
-- The 'strict' paramter automatically handles NULL inputs

CREATE FUNCTION smoosh(text, text, text) RETURNS text
  VOLATILE STRICT
AS $$$
DECLARE
  BEGIN
    IF  character_length($1) > 0 THEN
      RETURN $1 || $3 || $2;
    ELSE
      RETURN $2;
    END IF;
  END;
$$$ LANGUAGE plpgsql;

-- Combine text with supplied delimiter
-- eg smoosh(name, ', ')
CREATE AGGREGATE smoosh(text, text)
(
  SFUNC = smoosh,
  STYPE = text
);

-- TEST HERE
/*
select smoosh(i::text, ', ')
FROM
  (select generate_series(1, 3) as i UNION select null as i UNION select 4 as i) foo
*/

Aggregates take a bit of brain power to interpret, but basically:

• The Smoosh FUNCTION concatenates two input strings, with a supplied delimiter in the middle
• The Smoosh AGGREGATE just tells PostgreSQL to call the Smoosh FUNCTION for each row, and that it’s of type text

Neat, eh?!