Pass array of mixed type into stored FUNCTION

I am preparing procedure, that is basically finishing payment. It takes all info as arguments and do all inserts and stuff in one transaction.

the problem here is, that one argument is list of n arrays (basically list of items that were bought), so I have to make my FUNCTION eat array of arrays.

The solution given by Erwin Brandstetter from question Use array of composite type as function parameter and access it is basically what I need, except one thing. My target table has to have also column: id SERIAL PRIMARY KEY

I have been trying to modify it slightly:

This is target table:

CREATE TABLE cashreg_journal (
  id SERIAL PRIMARY KEY,
  gid INTEGER NOT NULL,
  device_id TEXT NOT NULL,
  item TEXT NOT NULL,
  vat_percentage INTEGER NOT NULL,
  vat_sum INTEGER, price NUMERIC);

This is function:

CREATE OR REPLACE FUNCTION test_insert(daco TEXT, VARIADIC _journal_arr cashreg_journal[]) RETURNS TEXT
  LANGUAGE plpgsql
  AS $function$

DECLARE

  jr cashreg_journal;

BEGIN

  FOREACH jr IN ARRAY _journal_arr LOOP
    INSERT INTO cashreg_journal(gid, device_id, item, vat_percentage, vat_sum, price)
    SELECT jr.*;

  END LOOP;

  RAISE NOTICE 'Daco: %', daco;

  RETURN 'Saved';

END;
$function$;

but it is not working this way:

SELECT * FROM test_insert('Test daco', '(1,000-111,Test-P,20,20,100)', '(1,000-111,Test-F,10,10,100)');
ERROR:  invalid input syntax for integer: "000-111"
LINE 1: SELECT * FROM test_insert('Test daco', '(1,000-111,Test-P,20...
                                               ^

so it seems, that I do not fully understand how it works and my mind ways are not right.

Could anybody please give me some hint, to point me in the right direction? :)

Edit:
I am working on many machnes, however the postgres version is always 9.4, 9.5 or 9.6.
I have also forget to mention that what I like about given solution is, that I do not need to create new type. Which I try to avoid.

The solution I was looking for is this:

CREATE OR REPLACE FUNCTION test_insert(daco TEXT, _journal_arr cashreg_journal[]) RETURNS TEXT
  LANGUAGE plpgsql
  AS $function$

DECLARE

  jr cashreg_journal;

BEGIN

  FOREACH jr IN ARRAY _journal_arr LOOP
    INSERT INTO cashreg_journal(gid, device_id, item, vat_percentage, vat_sum, price)
    SELECT jr.gid, jr.device_id, jr.item, jr.vat_percentage, jr.vat_sum, jr.price;

  END LOOP;

  RAISE NOTICE 'Daco: %', daco;

  RETURN 'Saved';

END;
$function$;

I can specify columns and don't need to create new type. I have also removed VARIADIC as it is messing with parameters when FUNCTION has more than one input.

The new call and result is:

test=# SELECT test_insert('Test daco', '{"(1,1,000-111,Test-P,20,20,100)","(1,1,000-111,Test-F,10,10,100)"}'::cashreg_journal[]);
NOTICE:  Daco: Test daco
 test_insert 
-------------
 Saved
(1 row)

test=# 
test=# 
test=# SELECT * FROM cashreg_journal;
 id | gid | device_id |  item  | vat_percentage | vat_sum | price 
----+-----+-----------+--------+----------------+---------+-------
  1 |   1 | 000-111   | Test-P |             20 |      20 |   100
  2 |   1 | 000-111   | Test-F |             10 |      10 |   100
  3 |   1 | 000-111   | Test-P |             20 |      20 |   100
  4 |   1 | 000-111   | Test-F |             10 |      10 |   100
  5 |   1 | 000-111   | Test-P |             20 |      20 |   100
  6 |   1 | 000-111   | Test-F |             10 |      10 |   100
  7 |   1 | 000-111   | Test-P |             20 |      20 |   100
  8 |   1 | 000-111   | Test-F |             10 |      10 |   100
  9 |   1 | 000-111   | Test-P |             20 |      20 |   100
 10 |   1 | 000-111   | Test-F |             10 |      10 |   100
(10 rows)

test=#

the trick is, that I have to give some value in first column ID, but the FUNCTION is just ignoring it.

If I don't provide this dummy value, FUNCTION raises exception:

test=# SELECT test_insert('Test daco', '{"(1,000-111,Test-P,20,20,100)","(1,000-111,Test-F,10,10,100)"}'::cashreg_journal[]);
ERROR:  invalid input syntax for integer: "000-111"
LINE 1: SELECT test_insert('Test daco', '{"(1,000-111,Test-P,20,20,1...
                                        ^
test=#

StackExchange Database Administrators Q#224785, answer score: 2