On 31 August 2012 07:59, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
wrote:
> On 30 August 2012 20:05, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Sun, Aug 12, 2012 at 5:14 PM, Dean Rasheed
>> <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> Here's an updated patch for the base feature (without support for
> security barrier views) with updated docs and regression tests.
Please find the review of the patch.
Basic stuff:
----------------------
- Patch applies OK
- Compiles cleanly with no warnings
- Regression tests pass.
- Documentation changes are mostly fine.
What it does:
------------------------
The non-select DML operations on views which are simple updatable (The
condition checks can be found in test section), can rewrite the query and
execute it even if the view don't have rules and instead of triggers.
Testing:
----------------
The following test is carried out on the patch.
1. create a view which can be automatically updated.
- no of view columns are not matching with actual base relation
- column order should be different with base relation order
- view column aliases as another column name
- view with a where condition
- column contains some constraints.
- ORDER BY
- FOR
2. create a view with all the features where automatically update is not
possible.
- Distinct clauses
- Every TLE is not a column reference.
- TLE appears more than once.
- Refers to more than one base relation.
- Other than relation in FROM clause.
- GROUP BY or HAVING clauses.
- set operations (UNION, INTERSECT or EXCEPT).
- sub-queries in the WHERE clause.
- DISTINCT ON clauses.
- window functions.
- CTEs (WITH or WITH RECURSIVE).
- OFFSET or LIMIT clauses.
- system columns.
- security_barrier;
- refers to whole rows of a table.
- column permissions are not there for users.
- refers to a sequence instead of relation.
-
3. create a view which is having instead of triggers.
4. create a view which is having rules.
5. create a view on a base relation which is also a view of automatically
updated.
6. create a view on a base relation which is also a view having instead of
triggers.
7. create a view on a base relation which is also a view having rules.
Extra test cases that can be added to regression suite are as below:
1. where clause in view select statement
2. ORDER BY, FOR, FETCH.
3. Temp views, views on temp tables.
4. Target entry JOIN, VALUES, FUNCTION
5. Toast column
6. System view
7. Lateral and outer join
8. auto increment columns
9. Triggers on tables
10.View with default values
11.Choosing base relation based on schema.
12.SECURITY DEFINER function execution
The updated regression test sql file with extra test is attached with this
mail, please check and add it to the patch.
Code Review:
------------------------
1. In test_auto_update_view function
if (var->varattno == 0)
return "Views that refer to whole rows from the base relation
are not updatable";
I have a doubt that when the above scenario will cover? And the examples
provided for whole row are working.
2. In test_auto_update_view function
if (base_rte->rtekind != RTE_RELATION)
return "Views that are not based on tables or views are not
updatable";
for view on sequences also the query is rewritten and giving error while
executing.
Is it possible to check for a particular relkind before rewriting query?
3. In function rewriteTargetView
if (tle->resjunk || tle->resno <= 0)
continue;
The above scenario is not possible as the junk is already removed in above
condition and also
the view which is refering to the system columns are not auto update views.
4. In function rewriteTargetView
if (view_tle == NULL)
elog(ERROR, "View column %d not found", tle->resno);
The parsetree targetlist is already validated with view targetlist during
transformstmt.
Giving an ERROR is fine here? Shouldn't it be Assert?
5. if any derived columns are present on the view, at least UPDATE operation
can be allowed for columns other than derived columns.
6. name test_auto_update_view can be changed. The word test can be changed.
7. From function get_view_query(), error message : "invalid _RETURN rule action
specification" might not make much sense to user
who is inserting in a view.
Defects from test
---------------------------
1. With a old database and new binaries the following test code results in
wrong way.
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;
SELECT table_name, is_updatable, is_insertable_into
FROM information_schema.views where table_name = 'rw_view1';
This will show is_insertable_into as 'no'. However below SQL statement is
success
INSERT INTO rw_view1 VALUES (3, 'Row 3');
2. User-1:
Table and views are created under user-1.
Grant select and insert permission to user-2 on table and view.
Alter the view owner as user-3.
User-2:
Try to insert into the view is failing because of permission's even
though user-2 has rights on both table and view.
Documentation Improvements
--------------------------------------------
1. Under title Updateable Views -
If the view satisifes all these conditions then it will be automatically
updatable.
This seems to be appearing both before and after the conditions of
non-updateable views.
2. Grant of rights on views should be mentioned separatly.
With Regards,
Amit Kapila.
--
-- 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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
