CODEWITHSUNDEEP
postgresqltriggers
SS_Rebelious
SS_Rebelious

Reputation: 1364

How to create trigger on delete from table to drop corresponding view?

I want to create trigger that drops the view when the row that contains name of that view is deleted from table.

Consider following table, view.

CREATE TABLE views
(
  id serial NOT NULL, 
  name character varying(128) NOT NULL
  CONSTRAINT views_pkey PRIMARY KEY (id),
  CONSTRAINT views_name_unique UNIQUE (name)
);

CREATE OR REPLACE VIEW test AS 
SELECT * FROM views;

INSERT INTO views (name) VALUES('test');

I create trigger to drop view on delete from table views:

CREATE OR REPLACE FUNCTION drop_view()

RETURNS trigger AS
$BODY$
DECLARE

viewname varchar(128);

BEGIN
    viewname := OLD.name;
    DROP VIEW IF EXISTS viewname;
    RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION drop_spatial_view()
  OWNER TO postgres;


CREATE TRIGGER del
  AFTER DELETE
  ON views
  FOR EACH ROW
  EXECUTE PROCEDURE drop_view();

When I delete row from views I get an error:

DELETE FROM views WHERE name = 'test';

NOTICE:  view "viewname" does not exist, skipping
CONTEXT:  SQL statement "DROP VIEW IF EXISTS viewname"

Seems that there is an issue with assignment of value test to variable viewname. How to rewrite trigger to make it work?

Upvotes: 2

Views: 552

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175636

To use DDL syntax inside routines you need to use Dynamic-SQL.

Change:

DROP VIEW IF EXISTS viewname;

to:

EXECUTE 'DROP VIEW IF EXISTS ' || viewname;

Upvotes: 2

Related Questions