The following bug has been logged on the website:
Bug reference: 8275
Logged by: Rushabh Lathia
Email address: rushabh.lathia@gmail.com
PostgreSQL version: 9.2.4
Operating system: All
Description:
View based on inheritance throws error on insert statement.
Testcase:
DROP TABLE tp_sales cascade;
CREATE TABLE tp_sales
(
salesman_id INT4,
salesman_name VARCHAR,
sales_region VARCHAR,
sales_amount INT4
);
create table tp_sales_p_india ( check (sales_region = 'INDIA')) inherits
(tp_sales);
create table tp_sales_p_rest (check (sales_region != 'INDIA')) inherits
(tp_sales);
CREATE OR REPLACE FUNCTION tp_sales_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.sales_region = 'INDIA' THEN
INSERT INTO tp_sales_p_india VALUES (NEW.*);
ELSE
INSERT INTO tp_sales_p_rest VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_tp_sales_trigger
BEFORE INSERT ON tp_sales
FOR EACH ROW EXECUTE PROCEDURE tp_sales_insert_trigger();
INSERT INTO tp_sales VALUES (100,'Foo','INDIA',15000);
INSERT INTO tp_sales VALUES (110,'Bar','UK',24000);
CREATE view view_tp_sales as SELECT * FROM tp_sales;
-- run insert on view
postgres=# INSERT INTO view_tp_sales VALUES (120,'XYZ','INDIA',11000);
ERROR: new row for relation "tp_sales_p_rest" violates check constraint
"tp_sales_p_rest_sales_region_check"
DETAIL: Failing row contains (120, XYZ, INDIA, 11000).
postgres=# INSERT INTO view_tp_sales VALUES (120,'ABC','HELLO',11000);
ERROR: new row for relation "tp_sales_p_india" violates check constraint
"tp_sales_p_india_sales_region_check"
DETAIL: Failing row contains (120, ABC, HELLO, 11000).
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
(1 row)
