Postgresql – How to use an array as argument to a VARIADIC function in PostgreSQL

I answered a similar question on SO some time ago, to suggest the same solution with unnest() like @a_horse did:

• Array of composite type as stored procedure input

If books_table has the same row type as your composite type books, you do not need to create the additional type at all, just use the row type of the table:

CREATE TABLE books_table (
  book_id numeric
, row_num numeric
, book_owner text
, book_owner_id text
);

PL/pgSQL function

If you need a plpgsql function for some undeclared reason:

CREATE OR REPLACE FUNCTION update_table_variadic(VARIADIC _books_arr books_table[])
  RETURNS TEXT
  LANGUAGE plpgsql AS
$func$
DECLARE
   b books_table;
BEGIN
   FOREACH b IN ARRAY _books_arr
   LOOP
      INSERT INTO books_table  -- rare case where column list is no improvement
      SELECT b.*;
   END LOOP;

   RETURN 'Saved';

EXCEPTION WHEN others THEN
   RETURN 'Failure';
END
$func$;

Example call with list of row values:

SELECT update_table_variadic('(2,100,Arthur,1)', '(2,50,Zaphod,1)');

Without using VARIADIC, the function call would require a single array parameter.

Either an array literal (optionally with explicit cast):

SELECT update_table('{"(1,100,Arthur,1)","(1,50,Zaphod,1)"}'::books_table[]);

See:

• How to call PostgreSQL function with array of rowtype as parameter from Java
• How to pass custom type array to Postgres function

Or you can use an array constructor like @a_horse demonstrates. Array literals are often easier to provide.

Major points:

Use the simpler FOREACH to loop over an array. See:

• Postgres - array for loop

Avoid CaMeL-case names in Postgres unless you know what you are doing.

(Optionally) use a VARIADIC parameter to simplify the syntax for the function call. Then you can provide a list of row values. Note the maximum number of function parameters (default 100) if you go with VARIADIC:

• Select rows such that names match elements of input array for pgsql function

SQL function

If you don't need to catch the exception, and you also don't need the string 'Saved' / 'Failure' to be returned, simplify:

CREATE OR REPLACE FUNCTION update_table_set(VARIADIC _books_arr books_table[])
  RETURNS void
  LANGUAGE sql AS
$func$
   INSERT INTO books_table
   SELECT * FROM unnest(_books_arr) b;
$func$;

db<>fiddle here
_{Old sqlfiddle}

The parameter _source in the MWE (minimal working example) is not referenced anywhere. The identifier source in the function body has no leading underscore and is interpreted as constant table name independently.

But it would not work like this anyway. SQL only allows to parameterize values in DML statements. See:

• Error when setting n_distinct using a plpgsql variable

Solution

You can still make it work using dynamic SQL with EXECUTE in a plpgsql function:

CREATE TYPE dataset AS (id integer, t timestamp, x float);

CREATE TABLE source OF dataset (PRIMARY KEY(Id));  -- add constraints in same command

INSERT INTO source VALUES
    (1, '2016-01-01 00:00:00', 10.0)
   ,(2, '2016-01-01 00:30:00', 11.0);

CREATE OR REPLACE FUNCTION process(_tbl regclass)
  RETURNS SETOF dataset AS
$func$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM ' || _tbl;
END
$func$  LANGUAGE plpgsql;

SELECT * FROM process('source');  -- table name as string literal 

See:

• Table name as a PostgreSQL function parameter

Or search for related questions and answers on site.

To make it work for any given table:

CREATE OR REPLACE FUNCTION process2(_tbl anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM ' || pg_typeof(_tbl);
END
$func$  LANGUAGE plpgsql;

SELECT * FROM process2(NULL::source);  -- note the call syntax!!

Detailed explanation:

• Refactor a PL/pgSQL function to return the output of various SELECT queries