From: | rushabh(dot)lathia(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement |
Date: | 2013-07-02 04:59:25 |
Message-ID: | E1UtsgX-0005d6-12@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 8275
Logged by: Rushabh Lathia
Email address: rushabh(dot)lathia(at)gmail(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Rushabh Lathia | 2013-07-02 07:44:40 | Re: BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement |
Previous Message | Peter Eisentraut | 2013-07-02 01:15:11 | Re: BUG #8271: Configure warning: sys/ucred.h: present but cannot be compiled |