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
);
我有上面的(非常简化的示例)表,我想在一个操作中插入订单的详细信息和订单的详细信息。
我熟悉事务,可以用下面的SQL命令插入数据:
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;
然而,理想情况下,如果可能的话,我希望有一个像上面的函数一样的查询,而不是存储在我的应用程序中。
有没有人可以指出我在正确的方向为postgres功能提供多个记录?或者,如果我想做的事情被认为是不好的做法,请让我知道我应该遵循的其他路线。
可以使用元组数组将多行传递给函数。您需要自定义类型:
create type order_input as (
item text,
quantity integer);
使用该类型的数组作为函数的实参:
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)
谢谢克林。这很有帮助。
此外,我能够避免使用显式类型,并且只使用定义为数组的表。
下面的代码:-- 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[]
);