It has been quite a while since the last post in this series but today comes the next one. Being at a customer this morning this question popped up: Can we have instead of triggers on a view in PostgreSQL as well? I couln’d immediately answer (although I was quite sure you can) so here is the test. I took an example for Oracle from here and re-wrote it in PostgreSQL syntax.
I took the same tables and adjusted the data types:
CREATE TABLE CUSTOMER_DETAILS ( CUSTOMER_ID INT PRIMARY KEY , CUSTOMER_NAME VARCHAR(20) , COUNTRY VARCHAR(20) ); CREATE TABLE PROJECTS_DETAILS ( PROJECT_ID INT PRIMARY KEY , PROJECT_NAME VARCHAR(30) , PROJECT_START_DATE DATE , CUSTOMER_ID INT REFERENCES CUSTOMER_DETAILS(CUSTOMER_ID) );
The same view definition:
CREATE OR REPLACE VIEW customer_projects_view AS SELECT cust.customer_id, cust.customer_name, cust.country, projectdtls.project_id, projectdtls.project_name, projectdtls.project_start_Date FROM customer_details cust, projects_details projectdtls WHERE cust.customer_id = projectdtls.customer_id;
Try to insert:
postgres=# INSERT INTO customer_projects_view VALUES (1,'XYZ Enterprise','Japan',101,'Library management',now()); ERROR: cannot insert into view "customer_projects_view" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule. Time: 2.135 ms
… and the answer is already in the error message. So obviously we should be able to do that. In PostgreSQL you need a trigger function:
CREATE OR REPLACE FUNCTION cust_proj_view_insert_proc() RETURNS trigger AS $$ BEGIN INSERT INTO customer_details (customer_id,customer_name,country) VALUES (NEW.customer_id, NEW.customer_name, NEW.country); INSERT INTO projects_details (project_id, project_name, project_start_Date, customer_id) VALUES ( NEW.project_id, NEW.project_name, NEW.project_start_Date, NEW.customer_id); RETURN NEW; EXCEPTION WHEN unique_violation THEN RAISE EXCEPTION 'Duplicate customer or project id'; END; $$ LANGUAGE plpgsql;
Then we need a trigger calling this function:
create trigger cust_proj_view_insert_trg instead of insert on customer_projects_view for each row EXECUTE procedure cust_proj_view_insert_proc();
Try the insert again:
INSERT INTO customer_projects_view VALUES (1,'XYZ Enterprise','Japan',101,'Library management',now()); INSERT INTO customer_projects_view VALUES (2,'ABC Infotech','India',202,'HR management',now());
… and here we are:
postgres=# select * FROM customer_details; customer_id | customer_name | country -------------+----------------+--------- 1 | XYZ Enterprise | Japan 2 | ABC Infotech | India
Definitely, you can 🙂