Postgres Function to insert multiple records in two tables

Question

create table public.orders (
    orderID serial PRIMARY KEY,
    orderdate timestamp NOT NULL
);

create table public.orderdetails (
    orderdetailID serial PRIMARY KEY,
    orderID integer REFERENCES public.orders(orderID),
    item varchar(20) NOT NULL,
    quantity INTEGER NOT NULL
);

I have (very simplified sample) tables as above, into which I want to insert details of an order and order details in one action.

I am familiar with transactions, and could insert data with an SQL command like the below:

DO $$
  DECLARE inserted_id integer;
  BEGIN
    INSERT INTO public.orders(orderdate) VALUES (NOW()) RETURNING orderID INTO inserted_id;

    INSERT INTO public.orderdetails(orderID, item, quantity)
    VALUES (inserted_id, 'Red Widget', 10),
           (inserted_id, 'Blue Widget', 5);
  END
$$ LANGUAGE plpgsql;

However, ideally I'd like to have a query like the above a function if possible, rather than being stored within my application.

Could anyone point me in the right direction for supplying multiple records to a postgres function? Alternatively, if what I am looking to do is considered bad practice, please let me know what other route I should follow.

Thanks in advance.

I really don't get the point of having this kind of relation, Why the need for orderdetails table? I would use one table and there is no problem with transaction — ad4s
– ad4s, Commented Sep 20, 2016 at 22:47
HI ad4s, Its a simplified example, the orders table will have stuff such as order date, shipping date, what shipping address, etc. while the orderdetails table has a breakdown item by item of whats ordered. — Devasta
– Devasta, Commented Sep 20, 2016 at 22:51
Possibly this one may help stackoverflow.com/questions/6822460/… — ad4s
– ad4s, Commented Sep 20, 2016 at 22:59

klin · Accepted Answer · 2016-09-20 23:07:15Z

You can use an array of tuples to pass multiple rows to the function. You need a custom type:

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

Use array of this type for an argument of the 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 $$;

Usage:

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

select * from orderdetails;

 orderdetailid | orderid |    item     | quantity 
---------------+---------+-------------+----------
             1 |       1 | Red Widget  |       10
             2 |       1 | Blue Widget |        5
(2 rows)

Is there a straightforward way to execute this function in pg-promise because javascript doesn't have a concept of tuples?

Syamjith · Accepted Answer · 2018-10-27 06:55:10Z

Thanks Klin. That helped a lot.

Further, I was able to avoid the usage of an explicit type and just having used the table defined as an array.

Code below:

-- Create table whose type will be passed as input parameter
create table tbl_card
(id integer,
name varchar(10),
cardno bigint)

-- Create function to accept an array of table
create or replace function fn_insert_card_arr (tbl_card[]) returns integer as $$
begin
insert into tbl_card (id, name,cardno)
select id, name, cardno
from unnest($1);

return 0;
end;
$$ LANGUAGE plpgsql;

-- Execute function by passing an array of table (type casted to array of type table)
select fn_insert_card_arr(
array[
    (1,'one', 2222777744448888), 
    (2,'two', 8888444466662222),
    (3,'three', 2222777744448888), 
    (4,'four', 8888444466662222)
]::tbl_card[]
);

Collectives™ on Stack Overflow

Postgres Function to insert multiple records in two tables

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related