0

I am working on a web application where users create copies of a table. They choose the name of the original table in a dropdown and the application creates a copy with a random name in the schema copy_tables.

The name of the copy table is inserted into the table config.copy_tables into the column copy_table_name.

There is no way for users to delete the copies. However an admin might manually delete an entry from config.copy_tables.

When that happens I would like to also drop the corresponding table in the schema copy_tables.

You find my attempt below. The copy_tables.OLD.copy_table_name part causes issues and I am not sure how to fix that. Basically I would like to:

  • drop the table in the schema copy_tables
  • whose name appeared in the column copy_table_name (config.copy_tables) in the row that was just deleted
CREATE OR REPLACE FUNCTION drop_copy_table()
RETURNS TRIGGER AS
$$
BEGIN
DROP TABLE copy_tables.OLD.copy_table_name;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_delete_copy_table ON config.copy_tables;

CREATE TRIGGER  trigger_delete_copy_table
AFTER DELETE ON config.copy_tables
FOR EACH ROW
EXECUTE PROCEDURE drop_copy_table();
BritishSteel
  • 171
  • 10
  • You need Dynamic SQL, that is SQL which writes SQL. Why? Because the command you wrote is to drop a table CALLED `copy_table_name` *(in the schema called `OLD`, in the database called `copy_tables`)*. An example is here: https://stackoverflow.com/questions/7914325/insert-with-dynamic-table-name-in-trigger-function – MatBailie May 07 '21 at 08:47
  • Thank you for the hint. Your explanation makes total sense. I will look into it. – BritishSteel May 07 '21 at 09:23

0 Answers0