Pass array of mixed type into stored FUNCTION

Question

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.

Answer 1

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=#

Answer 2

Following the link you have supplied and carefully reading the good answers of a_horse_with_no_name and Erwin Brandstetter I've tried to solve this question.

Use array of composite type as function parameter and access it

First I've created a composite TYPE:

CREATE TYPE cr_journal AS (
  gid INTEGER,
  device_id TEXT,
  item TEXT,
  vat_percentage INTEGER,
  vat_sum INTEGER, 
  price NUMERIC);

Then I've slightly edited your function:

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

DECLARE

    cj cr_journal;

BEGIN

    FOREACH cj IN array _journal_arr LOOP

        INSERT INTO cashreg_journal
            (gid, device_id, item, vat_percentage, vat_sum, price)
        SELECT cj.*;

    END LOOP;

  RAISE NOTICE 'Daco: %', daco;

  RETURN 'Saved';

END;
$function$;

And the last step, you should modify your select sentence, instead of SELECT * FROM function() you should call it as SELECT function() and you should explicitly cast the array as an array of type cr_journal[] in this way:

SELECT test_insert('Test daco', 
                   (ARRAY[(1, '000-111', 'Test-P', 20, 20, 100), 
                          (1, '000-111', 'Test-F', 20, 10, 100)])::cr_journal[]);

returns: Saved

Now you can check it:

select * from cashreg_journal;

And this is the result:

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 |             20 |      10 |   100

db<>fiddle here

Answer 3

Note that the simpler and faster set-based variant with unnest(), also demonstrated in the referenced answer, can be adapted to your use case in a similar fashion: by omitting the serial column in the INSERT. Core command:

INSERT INTO cashreg_journal
        (gid,   device_id,   item,   vat_percentage,   vat_sum,   price)
SELECT j.gid, j.device_id, j.item, j.vat_percentage, j.vat_sum, j.price
FROM   unnest(_journal_arr) j;

Also, about the statement in your answer:

I have also removed VARIADIC as it is messing with parameters when FUNCTION has more than one input.

That's probably a misunderstanding. This related answer from the next day might help:

• How to pass an array to a plpgsql function with VARIADIC parameter