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

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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]);

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 17, 2020 at 8:43

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Gus Over a year ago

Thanks for the suggestions, they were extremely useful! I added the event query and the executed query that got generated was select * from insert_into_orders(array[('Red Widget', 10), ('Blue Widget', 5)]). After this executed the error came back as { "error": { "message": "function insert_into_orders(text[]) does not exist" } } as this postgresql function is expecting insert_into_orders(order_input[]). Do you know if there is a way to map the input data to this order_input type? This is also for learning purposes, but I agree with your stored proc & transaction suggestion.

Gus Over a year ago

I tried the updated solution you proposed which gave me the following query select * from insert_into_orders (array['{"item":"Red Widget","quantity”:10}’]::thought_input[]) but I got the { "error": { "message": "malformed array literal: \"{\"item\":\"Red Widget\", \"quantity\":10}\"" } } error again. I wonder if it is because the sql function is expecting the data to be a tuple of list? Any thoughts?

vitaly-t Over a year ago

@Gus Try an extra cast maybe? Like ::json[]::order_input[]

Gus Over a year ago

unfortunately I could not get the casting from an object to a tuple to work. I simply used string interpolation to make the orderInput look like a tuple ('Red Widget', 10), ('Blue Widget', 5) since that is what the function was expecting and kept all of your other suggestions. I really appreciate all the help. Don't know who you are but stay safe and healthy man.

reads0520 Over a year ago

Like @Gus, couldn't get it to work with the json[] cast, but just using vitaly's values helper inside an array[...] to create the tuples, then casting to the param array type worked. So replacing with toPostgres: () => `array[${this.pgp.helpers.values(orderInput, ["item", "quantity"])}]::order_input[]`

|

Collectives™ on Stack Overflow

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

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related