iot_test.sql

text/x-sql

Download

DROP TABLE IF EXISTS foo CASCADE;
CREATE TABLE foo(a int PRIMARY KEY, b int, c int);
INSERT INTO foo
  SELECT g, g*10, g*100 FROM generate_series(1,10000) AS g;

CREATE VIEW foo_v AS
  SELECT b AS bb, c AS cc, a AS aa FROM foo WHERE a%2 = 0;

INSERT INTO foo_v (aa,bb,cc)
  SELECT g, g*10, g*100 FROM generate_series(10001,10005) AS g;

UPDATE foo_v SET cc=0 WHERE aa < 10;
EXPLAIN UPDATE foo_v SET cc=0 WHERE aa < 10;

DELETE FROM foo_v WHERE aa = 50;
EXPLAIN DELETE FROM foo_v WHERE aa = 50;

INSERT INTO foo_v (aa,bb,cc)
  SELECT g, g*10, g*100 FROM generate_series(10001,10005) AS g
  RETURNING aa,bb,cc;

CREATE VIEW foo_vv AS
  SELECT cc AS c, aa AS a, bb AS b FROM foo_v WHERE aa%3 = 0;

SELECT COUNT(*) FROM foo_vv;
SELECT * FROM foo_vv ORDER BY a LIMIT 5;

UPDATE foo_vv SET b=a*11 WHERE a=6;
EXPLAIN UPDATE foo_vv SET b=a*11 WHERE a=6;

CREATE VIEW foo_vvv AS
  SELECT a, b, (SELECT 1 FROM foo_vv AS vv2 WHERE vv2.a = vv1.b/12) AS c
    FROM foo_vv AS vv1;

DELETE FROM foo_vvv WHERE a=12;
EXPLAIN DELETE FROM foo_vvv WHERE a=12;

UPDATE foo_vvv SET c=NULL WHERE a=36;
EXPLAIN UPDATE foo_vvv SET c=NULL WHERE a=36;

INSERT INTO foo_vvv VALUES (1,2,3);
EXPLAIN INSERT INTO foo_vvv VALUES (1,2,3);

CREATE RULE foo_vv_rule AS ON UPDATE TO foo_vv
  DO INSTEAD SELECT 'foo_vv_rule doing nothing';

UPDATE foo_vv SET b=a*11 WHERE a=6;
EXPLAIN UPDATE foo_vv SET b=a*11 WHERE a=6;

UPDATE foo_vvv SET b=a*11 WHERE a=6;
EXPLAIN UPDATE foo_vvv SET b=a*11 WHERE a=6;

CREATE RULE foo_vvv_rule AS ON UPDATE TO foo_vvv
  DO INSTEAD UPDATE foo_v SET bb=NEW.b, cc=NEW.c WHERE aa=OLD.a;

UPDATE foo_vvv SET b=a*11 WHERE a >= 30 AND a <= 36;
EXPLAIN UPDATE foo_vvv SET b=a*11 WHERE a >= 30 AND a <= 36;

CREATE OR REPLACE RULE foo_vvv_rule AS ON UPDATE TO foo_vvv
  DO INSTEAD UPDATE foo_v SET bb=NEW.b, cc=NEW.c WHERE aa=OLD.a
             RETURNING NEW.a, NEW.b, NEW.c;

UPDATE foo_vvv SET b=a*11 WHERE a >= 30 AND a <= 36 RETURNING a, b, c;
EXPLAIN UPDATE foo_vvv SET b=a*11 WHERE a >= 30 AND a <= 36 RETURNING a, b, c;