--
-- TEST CASE: references about view and table
--
SET DATESTYLE TO ISO;
CREATE TABLE cmpr_references_02(
        c_int_1 INTEGER NOT NULL,
        c_int_2 INTEGER NOT NULL,
        c_int_3 INTEGER NOT NULL,
        c_int_4 INTEGER NOT NULL,
        c_int_5 INTEGER NOT NULL,
        c_int_6 INTEGER NOT NULL,
        c_int_7 INTEGER NOT NULL,
        c_int_8 INTEGER NOT NULL,
        c_int_9 INTEGER NOT NULL,
        c_int_10 INTEGER NOT NULL,
        c_VARCHAR_11 VARCHAR(120) NOT NULL,
        c_VARCHAR_12 VARCHAR(120) NOT NULL
)WITH (FILLFACTOR=90);
CREATE TABLE cmpr_references_f03(
        c_int_1 INTEGER UNIQUE,
        c_VARCHAR_2 VARCHAR(120) PRIMARY KEY
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "cmpr_references_f03_pkey" for table "cmpr_references_f03"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "cmpr_references_f03_c_int_1_key" for table "cmpr_references_f03"
CREATE TABLE cmpr_references_03(
        c_int_1 INTEGER,
        c_int_2 INTEGER,
        c_int_3 INTEGER,
        c_int_4 INTEGER,
        c_int_5 INTEGER,
        c_int_6 INTEGER,
        c_int_7 INTEGER,
        c_int_8 INTEGER,
        c_int_9 INTEGER,
        c_int_10 INTEGER,
        c_VARCHAR_11 VARCHAR(120),
        c_VARCHAR_12 VARCHAR(120))WITH (FILLFACTOR=90) ;
INSERT INTO cmpr_references_02 VALUES (generate_series(1,10000,10),generate_series(1,10000,10),generate_series(2,10000,10),generate_series(2,10000,10),generate_series(2,10000,10),generate_series(2,10000,10),generate_series(1,10000,10),generate_series(2,10000,10),generate_series(1,10000,10),generate_series(2,10000,10),'aaa'||MOD(generate_series(6,10000,10),200),'aaa'||MOD(generate_series(6,10000,10),200));
INSERT INTO cmpr_references_f03 VALUES (generate_series(1,10000,10),'aaa'||generate_series(6,10000,10));
COPY cmpr_references_02 TO '@abs_srcdir@/data/cmpr_references_02.data';
COPY cmpr_references_03 FROM '@abs_srcdir@/data/cmpr_references_02.data';
(SELECT * FROM cmpr_references_02) MINUS ALL (SELECT * FROM cmpr_references_03);
 c_int_1 | c_int_2 | c_int_3 | c_int_4 | c_int_5 | c_int_6 | c_int_7 | c_int_8 | c_int_9 | c_int_10 | c_varchar_11 | c_varchar_12 
---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+--------------+--------------
(0 rows)

(SELECT * FROM cmpr_references_03) MINUS ALL (SELECT * FROM cmpr_references_02);
 c_int_1 | c_int_2 | c_int_3 | c_int_4 | c_int_5 | c_int_6 | c_int_7 | c_int_8 | c_int_9 | c_int_10 | c_varchar_11 | c_varchar_12 
---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+--------------+--------------
(0 rows)

-- create and use view to update
CREATE OR REPLACE VIEW cmpr_references_v03( ref_1, ref_2) AS SELECT c_int_1, c_VARCHAR_11 FROM cmpr_references_03;
UPDATE cmpr_references_v03 SET ref_1=0, ref_2='aaa' WHERE ref_2='aaa'||6;
ERROR:  cannot update view "cmpr_references_v03"
HINT:  You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.
SELECT COUNT(*) FROM cmpr_references_v03 WHERE ref_2='aaa';
 count 
-------
     0
(1 row)

SELECT COUNT(*) FROM cmpr_references_v03 WHERE ref_2='aaa'||6;
 count 
-------
    50
(1 row)

INSERT INTO cmpr_references_f03 VALUES(0,'aaa');
UPDATE cmpr_references_v03 SET ref_1=0, ref_2='aaa' WHERE ref_2='aaa'||6;
ERROR:  cannot update view "cmpr_references_v03"
HINT:  You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.
SELECT COUNT(*) FROM cmpr_references_v03 WHERE ref_2='aaa'||6;
 count 
-------
    50
(1 row)

SELECT COUNT(*) FROM cmpr_references_v03 WHERE ref_2='aaa';
 count 
-------
     0
(1 row)

DROP TABLE cmpr_references_02;
DROP TABLE cmpr_references_03 CASCADE;
NOTICE:  drop cascades to view cmpr_references_v03
DROP TABLE cmpr_references_f03;
\! rm -fr @abs_srcdir@/data/cmpr_references_02.data