Postgresql – Use array of composite type as function parameter and access it

arraycomposite-typesfunctionsplpgsqlpostgresql

I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table.

This is my type:

CREATE TYPE Books AS (
V_Book_ID NUMERIC,
V_Row_Num NUMERIC,
V_Book_OWNER TEXT,
V_Book_OWNER_ID TEXT
);

This is my function:

CREATE OR REPLACE FUNCTION Update_Table(row_book Books[]) RETURNS TEXT AS $$
DECLARE
   Status TEXT;
   I_Max integer := array_length(row_book, 1);
BEGIN
FOR I in 1..I_Max
  LOOP
   INSERT INTO books_table(Book_ID,
   Row_Num,
   Book_OWNER,
   Book_OWNER_ID)
   values
   (row_book[I].V_Book_ID,
   row_book[I].V_Row_Num,
   row_book[I].V_Book_OWNER,
   row_book[I].V_Book_OWNER_ID);
END LOOP;

   STATUS:='Saved';
exception when others then
   STATUS:='failure';
   RETURN STATUS;

END;
$$ language plpgsql;

How do I send data to the function or how should I call the function with data?

Best Answer

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

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:

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:

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:

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

Related Question