Execute Postgresql function that accepts an array of parameters using pg-promise

Question

We have this postgresql type:

create type order_input as (
    item text,
    quantity integer);

And this postgresql function:

create or replace function insert_into_orders(order_input[])
returns void language plpgsql as $$
declare 
    inserted_id integer;
begin
    insert into public.orders(orderdate) 
    values (now()) 
    returning orderid into inserted_id;

    insert into public.orderdetails(orderid, item, quantity)
    select inserted_id, item, quantity
    from unnest($1);
end $$;

To execute in pgadmin-4 we run:

select insert_into_orders(
    array[
        ('Red Widget', 10), 
        ('Blue Widget', 5)
    ]::order_input[]
);

I am trying to figure out how to execute the insert_into_orders function using the pg-promise javascript library. I've tried doing the following:

const pgp = require("pg-promise")();
const db = pgp(connectionObj);

await db.func("insert_into_orders", [{item:"Red Widget", quantity:10}, {item:"Blue Widget", quantity:5}]

but getting the following message:

{
  "error": {
    "message": "malformed array literal: \"{\"item\":\"Red Widget\", \"quantity\":10}\""
  }
}

Would really appreciate if anyone knew how I had to structure my input for pg-promise, the original post is from here: Postgres Function to insert multiple records in two tables

Answer 1

Method func expects an array of values as the second parameter. In your case it must be one parameter - array of objects. But you instead give it 2 parameters, each an object, hence the error.

The correct way is:

const orderInput = [{item:"Red Widget", quantity:10}, {item:"Blue Widget", quantity:5}];

await db.func('insert_into_orders', [orderInput]);

Anyhow, you should use event query or pg-monitor to see what SQL was generated.

Also, your insert_into_orders should be a stored procedure, and then executed via proc, at the very least. However, its implementation looks like it should be just a transaction within your code.

After this executed the error came back as "error": {"message": "function insert_into_orders(text[]) does not exist"}

That's because of the type mismatch, which requires type casting...

You can either invoke the function via a regular query method, or make use of Custom Type Formatting to modify the data format:

const data = {
    toPostgres: () => `${pgp.as.array(orderInput)}::json[]::order_input[]`,
    rawType: true
};

Then pass it in instead:

await db.func('insert_into_orders', [data]);