Postgresql – Pass array of mixed type into stored FUNCTION

arraycomposite-typesfunctionsplpgsqlpostgresql

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.

Best Answer

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

Related Question