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!

Advertisement