Postgres函数在两个表中插入多个记录 [英] Postgres Function to insert multiple records in two tables
问题描述
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.
我对交易非常熟悉,可以使用如下所示的SQL命令插入数据:
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.
有人能为我指出向postgres函数提供多个记录的正确方向吗?另外,如果我想做的事情被认为是不好的做法,请让我知道我应该遵循的其他路线。
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.
预先感谢。
推荐答案
您可以使用元组数组将多个行传递给该函数。您需要一个自定义类型:
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 $$;
用法:
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)
这篇关于Postgres函数在两个表中插入多个记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!