diff options
Diffstat (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql')
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 61 |
1 files changed, 54 insertions, 7 deletions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index cdfd9c960e5..e1b955f3f0d 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -1262,27 +1262,74 @@ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; -- test WITH CHECK OPTION constraints -- =================================================================== +CREATE FUNCTION row_before_insupd_trigfunc() RETURNS trigger AS $$BEGIN NEW.a := NEW.a + 10; RETURN NEW; END$$ LANGUAGE plpgsql; + CREATE TABLE base_tbl (a int, b int); ALTER TABLE base_tbl SET (autovacuum_enabled = 'false'); +CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON base_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc(); CREATE FOREIGN TABLE foreign_tbl (a int, b int) - SERVER loopback OPTIONS(table_name 'base_tbl'); + SERVER loopback OPTIONS (table_name 'base_tbl'); CREATE VIEW rw_view AS SELECT * FROM foreign_tbl WHERE a < b WITH CHECK OPTION; \d+ rw_view -INSERT INTO rw_view VALUES (0, 10); -- ok -INSERT INTO rw_view VALUES (10, 0); -- should fail EXPLAIN (VERBOSE, COSTS OFF) -UPDATE rw_view SET b = 20 WHERE a = 0; -- not pushed down -UPDATE rw_view SET b = 20 WHERE a = 0; -- ok +INSERT INTO rw_view VALUES (0, 5); +INSERT INTO rw_view VALUES (0, 5); -- should fail +EXPLAIN (VERBOSE, COSTS OFF) +INSERT INTO rw_view VALUES (0, 15); +INSERT INTO rw_view VALUES (0, 15); -- ok +SELECT * FROM foreign_tbl; + +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = b + 5; +UPDATE rw_view SET b = b + 5; -- should fail EXPLAIN (VERBOSE, COSTS OFF) -UPDATE rw_view SET b = -20 WHERE a = 0; -- not pushed down -UPDATE rw_view SET b = -20 WHERE a = 0; -- should fail +UPDATE rw_view SET b = b + 15; +UPDATE rw_view SET b = b + 15; -- ok SELECT * FROM foreign_tbl; DROP FOREIGN TABLE foreign_tbl CASCADE; +DROP TRIGGER row_before_insupd_trigger ON base_tbl; DROP TABLE base_tbl; +-- test WCO for partitions + +CREATE TABLE child_tbl (a int, b int); +ALTER TABLE child_tbl SET (autovacuum_enabled = 'false'); +CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON child_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc(); +CREATE FOREIGN TABLE foreign_tbl (a int, b int) + SERVER loopback OPTIONS (table_name 'child_tbl'); + +CREATE TABLE parent_tbl (a int, b int) PARTITION BY RANGE(a); +ALTER TABLE parent_tbl ATTACH PARTITION foreign_tbl FOR VALUES FROM (0) TO (100); + +CREATE VIEW rw_view AS SELECT * FROM parent_tbl + WHERE a < b WITH CHECK OPTION; +\d+ rw_view + +EXPLAIN (VERBOSE, COSTS OFF) +INSERT INTO rw_view VALUES (0, 5); +INSERT INTO rw_view VALUES (0, 5); -- should fail +EXPLAIN (VERBOSE, COSTS OFF) +INSERT INTO rw_view VALUES (0, 15); +INSERT INTO rw_view VALUES (0, 15); -- ok +SELECT * FROM foreign_tbl; + +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = b + 5; +UPDATE rw_view SET b = b + 5; -- should fail +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = b + 15; +UPDATE rw_view SET b = b + 15; -- ok +SELECT * FROM foreign_tbl; + +DROP FOREIGN TABLE foreign_tbl CASCADE; +DROP TRIGGER row_before_insupd_trigger ON child_tbl; +DROP TABLE parent_tbl CASCADE; + +DROP FUNCTION row_before_insupd_trigfunc; + -- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== |