Postgresql – Postgres FOREACH array as composite type

composite-typespostgresql

I'm trying to loop over an array with two types as a composite type, but it seems that I can not use the composite type as the variable array to loop over it. What I'm trying to accomplish:

create type my_item as (
    field_1        timestamp,
    field_2        numeric
);

CREATE OR REPLACE FUNCTION unnest_2d_1d_my(ANYARRAY) 
  RETURNS TABLE (ts timestamp, value numeric) AS
$func$
DECLARE
  a my_item[];
  timestamp timestamp[];
  value1 numeric[];
BEGIN
   FOREACH a SLICE 1 IN ARRAY $1 LOOP
      timestamp = array_append(timestamp, a.field_1);
      value1 = array_append(value1,a.field_2);

   END LOOP;
   RETURN QUERY select unnest(timestamp), unnest(value1);   
END
$func$  
LANGUAGE plpgsql IMMUTABLE;

Composite array types -> (timestamp, numeric):

CREATE TABLE test AS(SELECT * FROM unnest_2d_1d_my(array[['2018-01-01',2],['2018-01-02',5]]));

I'm expecting as an output:

ts           | value
'2018-01-01' | 2
'2018-01-02' | 5

but currently I only obtain the following error:

invalid input syntax for integer: "2018-01-01"

I wonder how can I work with a multi-type array to achieve the mentioned output.

Best Answer

An array always contains a single data type. ['2018-01-01',2] specifies an array with two different types: a varchar and an integer.

To initialize an my_type value you need a row() constructor:

row('2018-01-01',2)::my_item

Elements of that type can then be put into an array:

array[row('2018-01-01',2)::my_item,row('2018-01-02',5)::my_item

or you can simplify that by casting the whole array, which saves you from repeating the ::my_type cast for each element:

array[row('2018-01-01',2),row('2018-01-02',5)]::my_item[]

If you have such an array, there is no need for your own function. An unnest on it will return the result you want:

SELECT x.* 
FROM unnest(array[row('2018-01-01',2),row('2018-01-02',5)]::my_item[]) as x;

returns:

field_1             | field_2
--------------------+--------
2018-01-01 00:00:00 |       2
2018-01-02 00:00:00 |       5

Related Solutions

Postgresql – Creating sequence for longer numeric values

If you can live with losing some values to the maximum value, you could combine a sequence with a fixed offset to get the 20 digits. I would also define a check constraint on the table to to make sure that accidental inserts without using the default value insert the wrong value:

create sequence my_sequence_name;
create table foo 
(
   id numeric(20,0) default 10000000000000000001 + nextval('my_sequence_name'),
   constraint check_range check (id between 10000000000000000001 and 99999999999999999999)
);

That will give you a maximum generated value of 19223372036854775808 which doesn't use the complete range of allowed values, but it might be enough for your needs.

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

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:

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}