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.