Skip site navigation (1) Skip section navigation (2)

BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement

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 (view raw or flat)
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)




Responses

pgsql-bugs by date

Next:From: Rushabh LathiaDate: 2013-07-02 07:44:40
Subject: Re: BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement
Previous:From: Peter EisentrautDate: 2013-07-02 01:15:11
Subject: Re: BUG #8271: Configure warning: sys/ucred.h: present but cannot be compiled

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group