Database Reference
In-Depth Information
Create the
sales_record_list1
table in the following manner:
warehouse_db=# CREATE TABLE sales_record_list1
(
PRIMARY KEY (id, city),
CHECK (city IN ('new york', 'sydney'))
)
INHERITS (sales_record_listpart);
Now, create the
sales_record_list2
table in the following manner:
warehouse_db=# CREATE TABLE sales_record_list2
(
PRIMARY KEY (id, city),
CHECK (city IN ('Islamabad', 'Boston', 'London'))
)
INHERITS (sales_record_listpart);
Let's create the index for the
sales_record_list1
table:
warehouse_db=# CREATE INDEX list1_index ON
sales_record_list1(city);
Let's create the index for the
sales_record_list2
table:
warehouse_db=# CREATE INDEX list2_index ON
sales_record_list2 (city);
Now, create the trigger function in the following manner:
warehouse_db=# CREATE OR REPLACE FUNCTION
sales_record_list_insert()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.city IN ('new york', 'sydney')) THEN
INSERT INTO sales_record_list1 VALUES (NEW.*);
ELSEIF (NEW.city IN ('Islamabad', 'Boston', 'London')) THEN
INSERT INTO sales_record_list2 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'CITY not present in this lists';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
