CREATE OR REPLACE RULE : Create Rule « Constraints « PostgreSQL

1. PostgreSQL
2. Constraints
3. Create Rule

CREATE OR REPLACE RULE

postgres=#
postgres=# CREATE TABLE measurement (
postgres(#    city_id         int not null,
postgres(#    logdate         date not null,
postgres(#    peaktemp        int,
postgres(#    unitsales       int
postgres(# );
CREATE TABLE
postgres=#
postgres=# CREATE TABLE measurement_yy04mm02 (
postgres(#     CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
postgres(# ) INHERITS (measurement);
CREATE TABLE
postgres=#
postgres=# CREATE TABLE measurement_yy04mm03 (
postgres(#     CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
postgres(# ) INHERITS (measurement);
CREATE TABLE
postgres=#
postgres=# CREATE OR REPLACE RULE measurement_current_partition AS
postgres-# ON INSERT TO measurement
postgres-# DO INSTEAD
postgres-#    INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id,
postgres(#                                              NEW.logdate,
postgres(#                                              NEW.peaktemp,
postgres(#                                              NEW.unitsales );
ERROR:  relation "measurement_yy06mm01" does not exist
postgres=#
postgres=# CREATE RULE measurement_insert_yy04mm02 AS
postgres-# ON INSERT TO measurement WHERE
postgres-#     ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
postgres-# DO INSTEAD
postgres-#     INSERT INTO measurement_yy04mm02 VALUES ( NEW.city_id,
postgres(#                                               NEW.logdate,
postgres(#                                               NEW.peaktemp,
postgres(#                                               NEW.unitsales );
CREATE RULE
postgres=#
postgres=# CREATE VIEW allmeasurement AS
postgres-# SELECT * FROM measurement_yy04mm02
postgres-# UNION ALL
postgres-# SELECT * FROM measurement_yy04mm03;
CREATE VIEW
postgres=#
postgres=# drop view allmeasurement cascade;
DROP VIEW
postgres=# drop table measurement_yy04mm02 cascade;
NOTICE:  drop cascades to rule measurement_insert_yy04mm02 on table measurement
DROP TABLE
postgres=# drop table measurement_yy04mm03 cascade;
DROP TABLE
postgres=# drop table measurement cascade;
DROP TABLE
postgres=#
postgres=#

           
       

Related examples in the same category