Postgres Function to insert multiple records in two tables

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)

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 parametercreate table tbl_card(id integer,name varchar(10),cardno bigint)-- Create function to accept an array of tablecreate or replace function fn_insert_card_arr (tbl_card[]) returns integer as $$begininsert into tbl_card (id, name,cardno)select id, name, cardnofrom 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[]);