Postgresql triggers to handle CRUD with rows and schemas

readme.md

1. Install Trigger functions
2. On the table whose rows will trigger these functions, install the trigger and select the trigger function.

auto_create_schema.sql

-- Trigger: auto_create_schema on landlord_tenant

-- DROP TRIGGER auto_create_schema ON landlord_tenant;

CREATE TRIGGER auto_create_schema
  AFTER INSERT
  ON landlord_tenant
  FOR EACH ROW
  EXECUTE PROCEDURE create_schema();

auto_drop_schema.sql

-- Trigger: auto_drop_schema on landlord_tenant

-- DROP TRIGGER auto_drop_schema ON landlord_tenant;

CREATE TRIGGER auto_drop_schema
  AFTER DELETE
  ON landlord_tenant
  FOR EACH ROW
  EXECUTE PROCEDURE drop_schema();

auto_rename_schema.sql

-- Trigger: auto_rename_schema on landlord_tenant

-- DROP TRIGGER auto_rename_schema ON landlord_tenant;

CREATE TRIGGER auto_rename_schema
  AFTER UPDATE
  ON landlord_tenant
  FOR EACH ROW
  EXECUTE PROCEDURE rename_schema();

create_schema.sql

-- Function: create_schema()

-- DROP FUNCTION create_schema();

CREATE OR REPLACE FUNCTION create_schema()
  RETURNS trigger AS
$BODY$

    BEGIN
	EXECUTE FORMAT('DROP SCHEMA IF EXISTS %I;', NEW.name);
	EXECUTE FORMAT('CREATE SCHEMA %I;', NEW.name);
        RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

delete_schema.sql

-- Function: delete_schema()

-- DROP FUNCTION delete_schema();

CREATE OR REPLACE FUNCTION delete_schema()
  RETURNS trigger AS
$BODY$
    BEGIN
	EXECUTE FORMAT('DROP SCHEMA IF EXISTS %I CASCADE;', OLD.name);
        RETURN OLD;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

update_schema.sql

-- Function: rename_schema()

-- DROP FUNCTION rename_schema();

CREATE OR REPLACE FUNCTION rename_schema()
  RETURNS trigger AS
$BODY$
    BEGIN
	EXECUTE FORMAT('ALTER SCHEMA %I RENAME TO %I;', OLD.name, NEW.name);
        RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;