PostgreSQL 继承表始终返回具有 Null 值的行
我指的是 http://www.if-not-true-then-false.com/2009/11/howto-create-postgresql-table-partitioning-part-1/
要重现该问题,这里是遵循一些简单的步骤:
(1) 创建名为“tutorial”的数据库
(2) 执行以下 SQL 查询:
CREATE TABLE impressions_by_day (
advertiser_id SERIAL NOT NULL,
day DATE NOT NULL DEFAULT CURRENT_DATE,
impressions INTEGER NOT NULL,
PRIMARY KEY (advertiser_id, day)
);
CREATE OR REPLACE FUNCTION insert_table()
RETURNS void AS
$BODY$DECLARE
_impressions_by_day impressions_by_day;
BEGIN
INSERT INTO impressions_by_day(impressions ) VALUES(888) RETURNING * INTO _impressions_by_day;
RAISE NOTICE 'After insert, the returned advertiser_id is %', _impressions_by_day.advertiser_id;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insert_table() OWNER TO postgres;
(3) 创建名为“tutorial_partition”的数据库
(4) 执行以下 SQL 查询:
CREATE TABLE impressions_by_day (
advertiser_id SERIAL NOT NULL,
day DATE NOT NULL DEFAULT CURRENT_DATE,
impressions INTEGER NOT NULL,
PRIMARY KEY (advertiser_id, day)
);
CREATE OR REPLACE FUNCTION insert_table()
RETURNS void AS
$BODY$DECLARE
_impressions_by_day impressions_by_day;
BEGIN
INSERT INTO impressions_by_day(impressions ) VALUES(888) RETURNING * INTO _impressions_by_day;
RAISE NOTICE 'After insert, the returned advertiser_id is %', _impressions_by_day.advertiser_id;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insert_table() OWNER TO postgres;
CREATE TABLE impressions_by_day_y2010m1ms2 (
PRIMARY KEY (advertiser_id, day),
CHECK ( day >= DATE '2010-01-01' AND day < DATE '2010-03-01' )
) INHERITS (impressions_by_day);
CREATE INDEX impressions_by_day_y2010m1ms2_index ON impressions_by_day_y2010m1ms2 (day);
CREATE OR REPLACE FUNCTION impressions_by_day_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.day >= DATE '2010-01-01' AND NEW.day < DATE '2010-03-01' ) THEN
INSERT INTO impressions_by_day_y2010m1ms2 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Something wrong with the impressions_by_day_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_impressions_by_day_trigger
BEFORE INSERT ON impressions_by_day
FOR EACH ROW EXECUTE PROCEDURE impressions_by_day_insert_trigger();
(5) 执行
SELECT * FROM insert_table() on tutorial
我们得到
通知:插入后,返回的advertiser_id为1
(6) 执行
SELECT * FROM insert_table() on tutorial_partition
我们得到
通知:插入后,返回的advertisement_id为
1 在tutorial_partition中,怎么可能让advertisement_id也为1?
I am referring to http://www.if-not-true-then-false.com/2009/11/howto-create-postgresql-table-partitioning-part-1/
To reproduce the problem, here is some simple steps to follow :
(1) create database named "tutorial"
(2) perform the following SQL query :
CREATE TABLE impressions_by_day (
advertiser_id SERIAL NOT NULL,
day DATE NOT NULL DEFAULT CURRENT_DATE,
impressions INTEGER NOT NULL,
PRIMARY KEY (advertiser_id, day)
);
CREATE OR REPLACE FUNCTION insert_table()
RETURNS void AS
$BODY$DECLARE
_impressions_by_day impressions_by_day;
BEGIN
INSERT INTO impressions_by_day(impressions ) VALUES(888) RETURNING * INTO _impressions_by_day;
RAISE NOTICE 'After insert, the returned advertiser_id is %', _impressions_by_day.advertiser_id;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insert_table() OWNER TO postgres;
(3) create database named "tutorial_partition"
(4) perform the following SQL query :
CREATE TABLE impressions_by_day (
advertiser_id SERIAL NOT NULL,
day DATE NOT NULL DEFAULT CURRENT_DATE,
impressions INTEGER NOT NULL,
PRIMARY KEY (advertiser_id, day)
);
CREATE OR REPLACE FUNCTION insert_table()
RETURNS void AS
$BODY$DECLARE
_impressions_by_day impressions_by_day;
BEGIN
INSERT INTO impressions_by_day(impressions ) VALUES(888) RETURNING * INTO _impressions_by_day;
RAISE NOTICE 'After insert, the returned advertiser_id is %', _impressions_by_day.advertiser_id;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insert_table() OWNER TO postgres;
CREATE TABLE impressions_by_day_y2010m1ms2 (
PRIMARY KEY (advertiser_id, day),
CHECK ( day >= DATE '2010-01-01' AND day < DATE '2010-03-01' )
) INHERITS (impressions_by_day);
CREATE INDEX impressions_by_day_y2010m1ms2_index ON impressions_by_day_y2010m1ms2 (day);
CREATE OR REPLACE FUNCTION impressions_by_day_insert_trigger()
RETURNS TRIGGER AS $
BEGIN
IF ( NEW.day >= DATE '2010-01-01' AND NEW.day < DATE '2010-03-01' ) THEN
INSERT INTO impressions_by_day_y2010m1ms2 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Something wrong with the impressions_by_day_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$
LANGUAGE plpgsql;
CREATE TRIGGER insert_impressions_by_day_trigger
BEFORE INSERT ON impressions_by_day
FOR EACH ROW EXECUTE PROCEDURE impressions_by_day_insert_trigger();
(5) execute
SELECT * FROM insert_table() on tutorial
We get
NOTICE: After insert, the returned advertiser_id is 1
(6) execute
SELECT * FROM insert_table() on tutorial_partition
We get
NOTICE: After insert, the returned advertiser_id is
How is it possible to get advertiser_id is 1 too, in tutorial_partition?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
触发器传回 NULL,这向 INSERT 指示不执行任何操作。由于未执行任何操作,因此 RETURNING * 子句不返回任何内容。您将无法拦截(并覆盖) INSERT 并在同一操作中使用 RETURNING 并获得任何有意义的内容。
The trigger is passing back NULL, which indicates to the INSERT that no action is to be performed. Since no action is performed, the RETURNING * clause returns nothing. You're not going to be able to intercept (and override) the INSERT and use RETURNING in the same operation and get anything meaningful.