DROP DATABASE
chinese_food_city;
CREATE DATABASE
chinese_food_city;
\c chinese_food_city
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 老家肉饼 刘老根
\.
\c chinese_food_city
CREATE OR REPLACE FUNCTION add(
IN p_a integer,
IN p_b integer,
OUT p_sum integer
)
AS
$BODY$
BEGIN
p_sum:= p_a + p_b; -- 进行加法运算,并将计算结果赋值给输出的参数p_sum
END;
$BODY$
LANGUAGE 'plpgsql' -- 指定函数的程序语言
VOLATILE -- 优化器不进行优化
RETURNS NULL ON NULL INPUT; -- 当传入参数含有null时返回null
CREATE TRIGGER after_insert_bill
AFTER INSERT ON bill
FOR EACH ROW
EXECUTE FUNCTION add(1,2);
INSERT INTO
bill(ic_id,dk_id,deal_time,price)
VALUES
('ic1034139',
'dk1003324',
'2023-09-30 11:38:24',
'20')
;
- 我明明定义了函数add,却无法定义触发器,报错如下。