--
-- UPDATABLE VIEWS
--

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

-- non-updatable views
CREATE VIEW ro_view1 AS SELECT DISTINCT a, b FROM base_tbl; -- DISTINCT not supported
CREATE VIEW ro_view2 AS SELECT a, b FROM base_tbl GROUP BY a, b; -- GROUP BY not supported
CREATE VIEW ro_view3 AS SELECT 1 FROM base_tbl HAVING max(a) > 0; -- HAVING not supported
CREATE VIEW ro_view4 AS SELECT count(*) FROM base_tbl; -- Aggregate functions not supported
CREATE VIEW ro_view5 AS SELECT a, rank() OVER() FROM base_tbl; -- Window functions not supported
CREATE VIEW ro_view6 AS SELECT a, b FROM base_tbl UNION SELECT -a, b FROM base_tbl; -- Set ops not supported
CREATE VIEW ro_view7 AS WITH t AS (SELECT a, b FROM base_tbl) SELECT * FROM t; -- WITH [RECURSIVE] not supported
CREATE VIEW ro_view8 AS SELECT a, b FROM base_tbl ORDER BY a OFFSET 1; -- OFFSET not supported
CREATE VIEW ro_view9 AS SELECT a, b FROM base_tbl ORDER BY a LIMIT 1; -- LIMIT not supported
CREATE VIEW ro_view10 AS SELECT 1 AS a; -- No base relations
CREATE VIEW ro_view11 AS SELECT b1.a, b2.b FROM base_tbl b1, base_tbl b2; -- Multiple base relations
CREATE VIEW ro_view12 AS SELECT * FROM generate_series(1, 10) AS g(a); -- SRF in rangetable
CREATE VIEW ro_view13 AS SELECT a, b FROM (SELECT * FROM base_tbl) AS t; -- Subselect in rangetable
CREATE VIEW ro_view14 AS SELECT ctid FROM base_tbl; -- System columns not supported
CREATE VIEW ro_view15 AS SELECT a, upper(b) FROM base_tbl; -- Expression/function in targetlist
CREATE VIEW ro_view16 AS SELECT a, b, a AS aa FROM base_tbl; -- Repeated column
CREATE VIEW ro_view17 AS SELECT * FROM ro_view1; -- Base relation not updatable
CREATE VIEW ro_view18 WITH (security_barrier = true)
  AS SELECT * FROM base_tbl; -- Security barrier views not updatable

SELECT table_name, is_insertable_into
  FROM information_schema.tables
 WHERE table_name LIKE 'ro_view%'
 ORDER BY table_name;

SELECT table_name, is_updatable, is_insertable_into
  FROM information_schema.views
 WHERE table_name LIKE 'ro_view%'
 ORDER BY table_name;

SELECT table_name, column_name, is_updatable
  FROM information_schema.columns
 WHERE table_name LIKE 'ro_view%'
 ORDER BY table_name, ordinal_position;

DELETE FROM ro_view1;
DELETE FROM ro_view2;
DELETE FROM ro_view3;
DELETE FROM ro_view4;
DELETE FROM ro_view5;
DELETE FROM ro_view6;
UPDATE ro_view7 SET a=a+1;
UPDATE ro_view8 SET a=a+1;
UPDATE ro_view9 SET a=a+1;
UPDATE ro_view10 SET a=a+1;
UPDATE ro_view11 SET a=a+1;
UPDATE ro_view12 SET a=a+1;
INSERT INTO ro_view13 VALUES (3, 'Row 3');
INSERT INTO ro_view14 VALUES (null);
INSERT INTO ro_view15 VALUES (3, 'ROW 3');
INSERT INTO ro_view16 VALUES (3, 'Row 3', 3);
INSERT INTO ro_view17 VALUES (3, 'ROW 3');
INSERT INTO ro_view18 VALUES (3, 'ROW 3');

DROP VIEW ro_view1, ro_view2, ro_view3, ro_view4, ro_view5,
          ro_view6, ro_view7, ro_view8, ro_view9, ro_view10,
          ro_view11, ro_view12, ro_view13, ro_view14, ro_view15,
          ro_view16, ro_view17, ro_view18;

-- simple updatable view
CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;

SELECT table_name, is_insertable_into
  FROM information_schema.tables
 WHERE table_name = 'rw_view1';

SELECT table_name, is_updatable, is_insertable_into
  FROM information_schema.views
 WHERE table_name = 'rw_view1';

SELECT table_name, column_name, is_updatable
  FROM information_schema.columns
 WHERE table_name = 'rw_view1'
 ORDER BY ordinal_position;

INSERT INTO rw_view1 VALUES (3, 'Row 3');
INSERT INTO rw_view1 (a) VALUES (4);
UPDATE rw_view1 SET a=0 WHERE a=1;
DELETE FROM rw_view1 WHERE b='Row 2';
SELECT * FROM base_tbl;

EXPLAIN (costs off) UPDATE rw_view1 SET a=1 WHERE a=0;
EXPLAIN (costs off) DELETE FROM rw_view1 WHERE a=0;

-- view on top of view
CREATE VIEW rw_view2 AS SELECT * FROM rw_view1;

SELECT table_name, is_insertable_into
  FROM information_schema.tables
 WHERE table_name = 'rw_view2';

SELECT table_name, is_updatable, is_insertable_into
  FROM information_schema.views
 WHERE table_name = 'rw_view2';

SELECT table_name, column_name, is_updatable
  FROM information_schema.columns
 WHERE table_name = 'rw_view2'
 ORDER BY ordinal_position;

SELECT * FROM base_tbl;
SELECT * FROM rw_view2;

INSERT INTO rw_view2 VALUES (5, 'Row 5');
INSERT INTO rw_view2 (a) VALUES (6);
SELECT * FROM rw_view2;
UPDATE rw_view2 SET b='Row 6' WHERE a=6;
DELETE FROM rw_view2 WHERE a=5;
SELECT * FROM rw_view2;

EXPLAIN (costs off) UPDATE rw_view2 SET a=1 WHERE a=0;
EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=0;

DROP VIEW rw_view1, rw_view2;

-- view on top of view with rules
CREATE VIEW rw_view1 AS SELECT * FROM base_tbl OFFSET 0; -- not updatable without rules/triggers
CREATE VIEW rw_view2 AS SELECT * FROM rw_view1;

SELECT table_name, is_insertable_into
  FROM information_schema.tables
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, is_updatable, is_insertable_into
  FROM information_schema.views
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, column_name, is_updatable
  FROM information_schema.columns
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name, ordinal_position;

CREATE RULE rw_view1_ins_rule AS ON INSERT TO rw_view1
  DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, NEW.b) RETURNING *;

SELECT table_name, is_insertable_into
  FROM information_schema.tables
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, is_updatable, is_insertable_into
  FROM information_schema.views
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, column_name, is_updatable
  FROM information_schema.columns
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name, ordinal_position;

CREATE RULE rw_view1_upd_rule AS ON UPDATE TO rw_view1
  DO INSTEAD UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a RETURNING NEW.*;

SELECT table_name, is_insertable_into
  FROM information_schema.tables
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, is_updatable, is_insertable_into
  FROM information_schema.views
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, column_name, is_updatable
  FROM information_schema.columns
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name, ordinal_position;

CREATE RULE rw_view1_del_rule AS ON DELETE TO rw_view1
  DO INSTEAD DELETE FROM base_tbl WHERE a=OLD.a RETURNING OLD.*;

SELECT table_name, is_insertable_into
  FROM information_schema.tables
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, is_updatable, is_insertable_into
  FROM information_schema.views
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, column_name, is_updatable
  FROM information_schema.columns
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name, ordinal_position;

SELECT * FROM rw_view2;
INSERT INTO rw_view2 VALUES (7, 'Row 7') RETURNING *;
UPDATE rw_view2 SET b='Row seven' WHERE a=7 RETURNING *;
SELECT * FROM rw_view2;
DELETE FROM rw_view2 WHERE a=7 RETURNING *;

-- view on top of view with triggers
DROP RULE rw_view1_ins_rule ON rw_view1;
DROP RULE rw_view1_upd_rule ON rw_view1;
DROP RULE rw_view1_del_rule ON rw_view1;

SELECT table_name, is_insertable_into
  FROM information_schema.tables
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, is_updatable, is_insertable_into,
       is_trigger_updatable, is_trigger_deletable,
       is_trigger_insertable_into
  FROM information_schema.views
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, column_name, is_updatable
  FROM information_schema.columns
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name, ordinal_position;

CREATE FUNCTION rw_view1_trig_fn()
RETURNS trigger AS
$$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO base_tbl VALUES (NEW.a, NEW.b);
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    DELETE FROM base_tbl WHERE a=OLD.a;
    RETURN OLD;
  END IF;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER rw_view1_ins_trig INSTEAD OF INSERT ON rw_view1
  FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();

SELECT table_name, is_insertable_into
  FROM information_schema.tables
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, is_updatable, is_insertable_into,
       is_trigger_updatable, is_trigger_deletable,
       is_trigger_insertable_into
  FROM information_schema.views
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, column_name, is_updatable
  FROM information_schema.columns
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name, ordinal_position;

CREATE TRIGGER rw_view1_upd_trig INSTEAD OF UPDATE ON rw_view1
  FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();

SELECT table_name, is_insertable_into
  FROM information_schema.tables
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, is_updatable, is_insertable_into,
       is_trigger_updatable, is_trigger_deletable,
       is_trigger_insertable_into
  FROM information_schema.views
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, column_name, is_updatable
  FROM information_schema.columns
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name, ordinal_position;

CREATE TRIGGER rw_view1_del_trig INSTEAD OF DELETE ON rw_view1
  FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();

SELECT table_name, is_insertable_into
  FROM information_schema.tables
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, is_updatable, is_insertable_into,
       is_trigger_updatable, is_trigger_deletable,
       is_trigger_insertable_into
  FROM information_schema.views
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name;

SELECT table_name, column_name, is_updatable
  FROM information_schema.columns
 WHERE table_name LIKE 'rw_view%'
 ORDER BY table_name, ordinal_position;

SELECT * FROM rw_view2;
INSERT INTO rw_view2 VALUES (7, 'Row 7') RETURNING *;
UPDATE rw_view2 SET b='Row seven' WHERE a=7 RETURNING *;
SELECT * FROM rw_view2;
DELETE FROM rw_view2 WHERE a=7 RETURNING *;

DROP VIEW rw_view1, rw_view2;

-- test whole row from view
CREATE VIEW rw_view1 AS SELECT b AS bb, a AS aa FROM base_tbl;

CREATE FUNCTION rw_view1_aa(x rw_view1)
  RETURNS int AS $$ SELECT x.aa $$ LANGUAGE sql;

UPDATE rw_view1 v SET bb='Updated row 6' WHERE rw_view1_aa(v)=6
  RETURNING rw_view1_aa(v), v.bb;
SELECT * FROM base_tbl;

EXPLAIN (costs off)
UPDATE rw_view1 v SET bb='Updated row 6' WHERE rw_view1_aa(v)=6
  RETURNING rw_view1_aa(v), v.bb;

DROP FUNCTION rw_view1_aa(rw_view1);
DROP VIEW rw_view1;
DROP TABLE base_tbl;

-- permissions checks
CREATE USER view_user1;
CREATE USER view_user2;

SET SESSION AUTHORIZATION view_user1;
CREATE TABLE base_tbl(a int, b text, c float);
INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);
CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
INSERT INTO rw_view1 VALUES ('Row 2', 2.0, 2);

GRANT SELECT ON base_tbl TO view_user2;
GRANT SELECT ON rw_view1 TO view_user2;
GRANT UPDATE (a,c) ON base_tbl TO view_user2;
GRANT UPDATE (bb,cc) ON rw_view1 TO view_user2;
RESET SESSION AUTHORIZATION;

SET SESSION AUTHORIZATION view_user2;
CREATE VIEW rw_view2 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
SELECT * FROM base_tbl; -- ok
SELECT * FROM rw_view1; -- ok
SELECT * FROM rw_view2; -- ok

INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- not allowed
INSERT INTO rw_view1 VALUES ('Row 3', 3.0, 3); -- not allowed
INSERT INTO rw_view2 VALUES ('Row 3', 3.0, 3); -- not allowed

UPDATE base_tbl SET a=a, c=c; -- ok
UPDATE base_tbl SET b=b; -- not allowed
UPDATE rw_view1 SET bb=bb, cc=cc; -- ok
UPDATE rw_view1 SET aa=aa; -- not allowed
UPDATE rw_view2 SET aa=aa, cc=cc; -- ok
UPDATE rw_view2 SET bb=bb; -- not allowed

DELETE FROM base_tbl; -- not allowed
DELETE FROM rw_view1; -- not allowed
DELETE FROM rw_view2; -- not allowed
RESET SESSION AUTHORIZATION;

SET SESSION AUTHORIZATION view_user1;
GRANT INSERT, DELETE ON base_tbl TO view_user2;
RESET SESSION AUTHORIZATION;

SET SESSION AUTHORIZATION view_user2;
INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- ok
INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- not allowed
INSERT INTO rw_view2 VALUES ('Row 4', 4.0, 4); -- ok
DELETE FROM base_tbl WHERE a=1; -- ok
DELETE FROM rw_view1 WHERE aa=2; -- not allowed
DELETE FROM rw_view2 WHERE aa=2; -- ok
SELECT * FROM base_tbl;
RESET SESSION AUTHORIZATION;

SET SESSION AUTHORIZATION view_user1;
REVOKE INSERT, DELETE ON base_tbl FROM view_user2;
GRANT INSERT, DELETE ON rw_view1 TO view_user2;
RESET SESSION AUTHORIZATION;

SET SESSION AUTHORIZATION view_user2;
INSERT INTO base_tbl VALUES (5, 'Row 5', 5.0); -- not allowed
INSERT INTO rw_view1 VALUES ('Row 5', 5.0, 5); -- ok
INSERT INTO rw_view2 VALUES ('Row 6', 6.0, 6); -- not allowed
DELETE FROM base_tbl WHERE a=3; -- not allowed
DELETE FROM rw_view1 WHERE aa=3; -- ok
DELETE FROM rw_view2 WHERE aa=4; -- not allowed
SELECT * FROM base_tbl;
RESET SESSION AUTHORIZATION;

DROP VIEW rw_view1, rw_view2;
DROP TABLE base_tbl;
DROP USER view_user1;
DROP USER view_user2;

-- WHERE clause in view select statement

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE VIEW rw_view1 AS SELECT * FROM base_tbl where a > 2;

INSERT INTO rw_view1 VALUES (3, 'Row 3');
INSERT INTO rw_view1 (a) VALUES (4);
UPDATE rw_view1 SET a=0 WHERE a=1;
DELETE FROM rw_view1 WHERE b='Row 2';
SELECT * FROM base_tbl;

DROP VIEW rw_view1;
DROP TABLE base_tbl;

-- ORDER BY

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl ORDER BY(a);

INSERT INTO rw_view1 VALUES (3, 'Row 3');
INSERT INTO rw_view1 (aa) VALUES (4);
UPDATE rw_view1 SET aa=0 WHERE aa=1;
DELETE FROM rw_view1 WHERE bb='Row 2';
SELECT * FROM base_tbl;

DROP VIEW rw_view1;
DROP TABLE base_tbl;

-- FOR

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl FOR UPDATE;

INSERT INTO rw_view1 VALUES (3, 'Row 3');
INSERT INTO rw_view1 (aa) VALUES (4);
UPDATE rw_view1 SET aa=0 WHERE aa=1;
DELETE FROM rw_view1 WHERE bb='Row 2';
SELECT * FROM base_tbl;

DROP VIEW rw_view1;
DROP TABLE base_tbl;

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl FOR SHARE;

INSERT INTO rw_view1 VALUES (3, 'Row 3');
INSERT INTO rw_view1 (aa) VALUES (4);
UPDATE rw_view1 SET aa=0 WHERE aa=1;
DELETE FROM rw_view1 WHERE bb='Row 2';
SELECT * FROM base_tbl;

DROP VIEW rw_view1;
DROP TABLE base_tbl;

-- FETCH

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl FETCH FIRST ROW ONLY;

INSERT INTO rw_view1 VALUES (3, 'Row 3');
INSERT INTO rw_view1 (aa) VALUES (4);
UPDATE rw_view1 SET aa=0 WHERE aa=1;
DELETE FROM rw_view1 WHERE bb='Row 2';
SELECT * FROM base_tbl;

DROP VIEW rw_view1;
DROP TABLE base_tbl;

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl FETCH NEXT ROW ONLY;

INSERT INTO rw_view1 VALUES (3, 'Row 3');
INSERT INTO rw_view1 (aa) VALUES (4);
UPDATE rw_view1 SET aa=0 WHERE aa=1;
DELETE FROM rw_view1 WHERE bb='Row 2';
SELECT * FROM base_tbl;

DROP VIEW rw_view1;
DROP TABLE base_tbl;

-- Temp views & views on Temp tables

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE TEMP TABLE temp_tbl (a int PRIMARY KEY, b text DEFAULT 'temp');
INSERT INTO temp_tbl VALUES (1, 'temp 1');
INSERT INTO temp_tbl VALUES (2, 'temp 2');

CREATE TEMP VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl;

INSERT INTO rw_view1 VALUES (3, 'Row 3');
INSERT INTO rw_view1 (aa) VALUES (4);
UPDATE rw_view1 SET aa=0 WHERE aa=1;
DELETE FROM rw_view1 WHERE bb='Row 2';
SELECT * FROM base_tbl;

CREATE VIEW rw_view2 AS SELECT a AS aa, b AS bb FROM temp_tbl;

INSERT INTO rw_view2 VALUES (3, 'temp 3');
INSERT INTO rw_view2 (aa) VALUES (4);
UPDATE rw_view2 SET aa=0 WHERE aa=1;
DELETE FROM rw_view2 WHERE bb='temp 2';
SELECT * FROM temp_tbl;

DROP VIEW rw_view2;
DROP TABLE temp_tbl;

DROP VIEW rw_view1;
DROP TABLE base_tbl;


-- Target entry JOIN, VALUES, FUNCTION

CREATE FUNCTION rw_view1_fn()
RETURNS int AS
$$
BEGIN
  return 1;
END;
$$
LANGUAGE plpgsql;

CREATE VIEW rw_view1 AS SELECT * FROM rw_view1_fn();

INSERT INTO rw_view1 VALUES (3);

DROP VIEW rw_view1;
DROP FUNCTION rw_view1_fn();

CREATE TABLE dept (
      deptno        NUMERIC(4) PRIMARY KEY,
      dname         VARCHAR(14),
      loc           VARCHAR(13));
 
CREATE TABLE emp (
      empno        NUMERIC(4) PRIMARY KEY,
      ename        VARCHAR(10),
      job          VARCHAR(9),
      mgr          NUMERIC(4),
      sal          NUMERIC(7,2),
      comm         NUMERIC(7,2),
      deptno       NUMERIC(2),
      FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));

CREATE VIEW emp_dept AS
      SELECT emp.empno, emp.ename, emp.deptno, emp.sal, dept.dname, dept.loc
      FROM emp, dept
      WHERE emp.deptno = dept.deptno
         AND dept.loc IN ('DALLAS', 'NEW YORK', 'BOSTON');

INSERT INTO emp_dept (ename, empno, deptno) VALUES ('KURODA', 9010, 40);
UPDATE emp_dept SET sal = sal * 1.10 WHERE deptno = 40;
DELETE FROM emp_dept WHERE ename = 'KURODA';

DROP VIEW emp_dept;
DROP TABLE emp;
DROP TABLE Dept;

CREATE TABLE dept (
      deptno        NUMERIC(4) PRIMARY KEY,
      dname         VARCHAR(14),
      loc           VARCHAR(13));

CREATE VIEW dept_values AS
      SELECT dept.deptno, dept.dname, dept.loc
      FROM dept, (VALUES(1,'DEV','DALLAS'), (2,'TEST','NEW YORK')) as dep(deptno, dname, loc)
      WHERE dept.loc IN ('DALLAS', 'NEW YORK');

INSERT INTO dept_values VALUES(3, 'INFO', 'LONDON');
UPDATE dept_values SET loc = 'TOKYO' WHERE loc = 'LONDON';
DELETE dept_values WHERE loc = 'TOKYO';

DROP VIEW dept_values;
DROP TABLE dept;


CREATE TABLE dept (
      deptno        NUMERIC(4) PRIMARY KEY,
      dname         VARCHAR(14),
      loc           VARCHAR(13));

CREATE VIEW dept_values AS
      SELECT dep.deptno, dep.dname, dep.loc
      FROM (VALUES(1,'DEV','DALLAS'), (2,'TEST','NEW YORK')) as dep(deptno, dname, loc)
      WHERE dep.loc IN ('DALLAS', 'NEW YORK');

INSERT INTO dept_values VALUES(3, 'INFO', 'LONDON');
UPDATE dept_values SET loc = 'TOKYO' WHERE loc = 'LONDON';
DELETE dept_values WHERE loc = 'TOKYO';

DROP VIEW dept_values;
DROP TABLE dept;

-- Toast table

CREATE TABLE base_tbl (a int PRIMARY KEY, b char(3000) DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl;

INSERT INTO rw_view1 VALUES (3, 'Row 3');
INSERT INTO rw_view1 (aa) VALUES (4);
UPDATE rw_view1 SET aa=0 WHERE aa=1;
DELETE FROM rw_view1 WHERE bb='Row 2';
SELECT a FROM base_tbl;

DROP VIEW rw_view1;
DROP TABLE base_tbl;


-- auto increment columns

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified', c serial);
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl;

INSERT INTO rw_view1 VALUES (3, 'Row 3');
INSERT INTO rw_view1 (aa) VALUES (4);
UPDATE rw_view1 SET aa=0 WHERE aa=1;
DELETE FROM rw_view1 WHERE bb='Row 2';
SELECT * FROM base_tbl;

DROP VIEW rw_view1;
DROP TABLE base_tbl;

-- system views

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

select tablename from pg_tables where tablename = 'base_tbl';
update pg_tables set tablename = 'test' where tablename = 'base_tbl';

DROP TABLE base_tbl;

-- lateral and outer joins

CREATE TABLE dept (
      deptno        NUMERIC(4) PRIMARY KEY,
      dname         VARCHAR(14),
      loc           VARCHAR(13));
 
CREATE TABLE emp (
      empno        NUMERIC(4) PRIMARY KEY,
      ename        VARCHAR(10),
      job          VARCHAR(9),
      mgr          NUMERIC(4),
      sal          NUMERIC(7,2),
      comm         NUMERIC(7,2),
      deptno       NUMERIC(2),
      FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));

CREATE VIEW emp_dept AS
      SELECT emp.empno, emp.ename, emp.deptno, emp.sal, dept.dname, dept.loc
      FROM emp right outer join dept on emp.deptno = dept.deptno;

INSERT INTO emp_dept (ename, empno, deptno) VALUES ('KURODA', 9010, 40);
UPDATE emp_dept SET sal = sal * 1.10 WHERE deptno = 40;
DELETE FROM emp_dept WHERE ename = 'KURODA';

DROP VIEW emp_dept;

CREATE VIEW emp_dept AS
      SELECT emp.empno, emp.ename, emp.deptno, emp.sal, dept.dname, dept.loc
      FROM emp left outer join dept on emp.deptno = dept.deptno;

INSERT INTO emp_dept (ename, empno, deptno) VALUES ('KURODA', 9010, 40);
UPDATE emp_dept SET sal = sal * 1.10 WHERE deptno = 40;
DELETE FROM emp_dept WHERE ename = 'KURODA';

DROP VIEW emp_dept;
DROP TABLE emp;
DROP TABLE Dept;


CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM lateral (select a, b from base_tbl) as base_tbl;

INSERT INTO rw_view1 VALUES (3, 'Row 3');
INSERT INTO rw_view1 (aa) VALUES (4);
UPDATE rw_view1 SET aa=0 WHERE aa=1;
DELETE FROM rw_view1 WHERE bb='Row 2';
SELECT * FROM base_tbl;

DROP VIEW rw_view1;
DROP TABLE base_tbl;

-- Table having triggers

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE FUNCTION rw_view1_trig_fn()
RETURNS trigger AS
$$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE base_tbl SET b=NEW.b WHERE a=1;
    RETURN NULL;
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER rw_view1_ins_trig AFTER INSERT ON base_tbl
  FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();

CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl;
  
INSERT INTO rw_view1 VALUES (3, 'Row 3');
select * from base_tbl;

DROP VIEW rw_view1;
DROP TRIGGER rw_view1_ins_trig on base_tbl;
DROP FUNCTION rw_view1_trig_fn();
DROP TABLE base_tbl;
  
  
-- View with default values

CREATE SEQUENCE seq;
CREATE TABLE base_tbl (a int PRIMARY KEY DEFAULT NEXTVAL('seq'), b text DEFAULT 'Unspecified');
INSERT INTO base_tbl(b) VALUES ('Row 1');
INSERT INTO base_tbl(b) VALUES ('Row 2');

CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl;
ALTER VIEW rw_view1 ALTER aa SET DEFAULT 10;
INSERT INTO rw_view1(bb) VALUES ('Row 3');
select * from base_tbl;

DROP VIEW rw_view1;
DROP TABLE base_tbl;
DROP SEQUENCE seq;


--schema selection for base tables

CREATE SCHEMA schema1;
CREATE SCHEMA schema2;

SET search_path='schema1';

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl;

SET search_path='schema2';

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (3, 'Row 3');
INSERT INTO base_tbl VALUES (4, 'Row 4');

SET search_path=schema2, schema1;
SELECT * FROM rw_view1;

INSERT INTO rw_view1 VALUES(5, 'row 5');
SELECT * FROM rw_view1;

SET search_path = "$user", public;
DROP SCHEMA schema1 CASCADE;
DROP SCHEMA schema2 CASCADE;


-- SECURITY DEFINER function execution

CREATE USER view_user1;
CREATE USER view_user2;

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl VALUES (1, 'Row 1');
INSERT INTO base_tbl VALUES (2, 'Row 2');

CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl;

GRANT INSERT ON rw_view1 TO view_user2;
GRANT SELECT ON base_tbl TO view_user2;

RESET SESSION AUTHORIZATION;
SET SESSION AUTHORIZATION view_user1;

CREATE FUNCTION rw_view1_aa(x int)
RETURNS int AS 
$$
BEGIN
    return x;
END; 
$$ 
LANGUAGE plpgsql
SECURITY DEFINER;

RESET SESSION AUTHORIZATION;
SET SESSION AUTHORIZATION view_user2;

INSERT INTO rw_view1(aa) VALUES (rw_view1_aa(3));
SELECT * FROM base_tbl;

RESET SESSION AUTHORIZATION;

DROP FUNCTION rw_view1_aa(int);
DROP VIEW rw_view1;
DROP TABLE base_tbl;

DROP USER view_user1;
DROP USER view_user2;