CREATE TABLE public.bill (
ic_id character varying(10),
dk_id character varying(10),
deal_time timestamp without time zone,
price numeric
);
CREATE TABLE public.stall (
dk_id character varying(10),
shop character varying(20),
shop_owner character varying(20)
);
COPY public.bill (ic_id, dk_id, deal_time, price) FROM stdin;
ic2034123 dk1000234 2023-09-30 11:30:01 15
ic2052342 dk1001584 2023-09-30 11:33:03 30
ic2034123 dk1001584 2023-09-30 11:34:04 3
ic2033233 dk1003324 2023-09-30 11:36:23 20
ic2035678 dk1000436 2023-09-30 11:36:44 20
ic2034434 dk1001584 2023-09-30 11:37:51 25
ic1034139 dk1003324 2023-09-30 11:38:23 20
\.
COPY public.stall (dk_id, shop, shop_owner) FROM stdin;
dk1000436 饭是钢 胡小喵
dk1000234 兰州拉面 刘老根
dk1003324 煎饼虎头军 胡小喵
dk1001584 老家肉饼 刘老根
\.
- 原始表格如上图所示
- 然后创建了函数
- 目标是判断店铺是否存在,如果不存在,则插入stall表
\c chinese_food_city
DROP FUNCTION insert_bill_and_stall;
CREATE OR REPLACE FUNCTION insert_bill_and_stall(
new_ic_id text,
new_dk_id text,
new_deal_time time,
new_price numeric,
new_shop text,
new_shop_owner text
)
RETURNS void AS $$
BEGIN
-- 检查档口是否已存在
IF EXISTS (SELECT 1 FROM public.stall WHERE dk_id = new_dk_id) THEN
-- 如果存在,只插入账单表
INSERT INTO public.bill (ic_id, dk_id, deal_time, price)
VALUES (new_ic_id, new_dk_id, new_deal_time, new_price);
ELSE
-- 如果不存在,插入档口表和账单表
INSERT INTO public.stall (dk_id, shop, shop_owner)
VALUES (new_dk_id, new_shop, new_shop_owner);
INSERT INTO public.bill (ic_id, dk_id, deal_time, price)
VALUES (new_ic_id, new_dk_id, new_deal_time, new_price);
END IF;
END;
$$ LANGUAGE plpgsql;
\c chinese_food_city
SELECT insert_bill_and_stall(
'ic1003532',
'dk1001584',
TIMESTAMP '2023-10-01 10:00:00',
10,
'老家肉饼',
'刘老根'
);