Pass more than 100 Arguments in a function using Array as parameter

Question

I need to create a stored procedure that checks if a particular accounts exists in the table If it exists

Need to create a custom function in PostgreSQL to update/insert a table. I need to pass around 200 values which unable to achieve. Some suggested to use Array as parameters. But its not working for me correctly. All 200 fields are combined with int, varchar, double, float etc. Also we cannot change the order.

Please tell me how to pass these as parameters and use them in update/insert statement.

My code looks something like this

create or replace function test(variadic text[])
....
Begin
Insert into customers (custno , company, firstname) values ($1[1],$1[2],$1[3]);
....
END;

So your function is not only variadic, but also polymorphic? What exactly are you trying to do in there, and how are you going to call it? — Bergi

Laurenz Albe Laurenz Albe · Accepted Answer · 2019-09-25T19:00:13

The best way to do that is to pass a composite type as argument.

You can use the type that is named like the table and automatically created with CREATE TABLE.

Here is a working example with 300 columns:

CREATE TABLE wide (
   i0 integer,
   t0 text,
   d0 date,
   i1 integer,
   t1 text,
   d1 date,
   i2 integer,
   t2 text,
   d2 date,
   i3 integer,
   t3 text,
   d3 date,
[... many missing columns]
   i99 integer,
   t99 text,
   d99 date
);

CREATE OR REPLACE PROCEDURE insert_wide(arg wide)
   LANGUAGE plpgsql AS
$$BEGIN
   INSERT INTO wide SELECT (arg).*;
END;$$;

CALL insert_wide(
        ROW(
           0,
           'atext',
           current_date + 0,
           1,
           'atext',
           current_date + 1,
           2,
           'atext',
           current_date + 2,
           3,
           'atext',
           current_date + 3,
[again, many missing columns]
           99,
           'atext',
           current_date + 99
        )
     );

But I think that if you have tables with that many columns, you are doing something wrong.

Pass more than 100 Arguments in a function using Array as parameter

1 Answers