0
I am trying to create a Trigger Function to remove a schema created in the database based on an idproj attribute from the geo.projects table listed below:
CREATE TABLE geo.projetos
(
idproj serial NOT NULL,
... , -- outros atributos
CONSTRAINT projetos_pkey PRIMARY KEY (idproj),
... -- Outras constraints,
);
The schema is created by a program (python/psycopg2) concatenating 'proj_' || idproj
. Thus, I would like to remove the corresponding schema when deleting the record in the table geo.projetos
.
For this purpose I created the following function:
CREATE OR REPLACE FUNCTION geo.drop_schema_projeto_id()
RETURNS trigger as $dsp$
DECLARE schema_to_drop text := 'proj_' || OLD.idproj::text;
BEGIN
DROP SCHEMA IF EXISTS schema_to_drop CASCADE;
RAISE NOTICE 'Schema a ser removido: %', schema_to_drop;
ETURN OLD;
END;
$dsp$ LANGUAGE plpgsql;
Creation of Trigger:
/*----------------------------------------------------------------*/
DROP TRIGGER drop_schema_projeto_id ON geo.projetos;
CREATE TRIGGER drop_schema_projeto_id
AFTER DELETE
ON geo.projetos
FOR EACH ROW
EXECUTE PROCEDURE geo.drop_schema_projeto_id();
However, when I run, for example:
DELETE FROM geo.projetos WHERE idproj = 87;
I receive the following message:
NOTA: esquema "schema_to_drop" não existe, ignorando
CONTEXTO: comando SQL "DROP SCHEMA IF EXISTS schema_to_drop CASCADE"
função PL/pgSQL geo.drop_schema_projeto_id() linha 5 em comando SQL
NOTA: Schema a ser removido: proj_87
DELETE 1
It seems that the problem lies in the manipulation of the variable schema_to_drop
which is not replaced by proj_87
.
I wonder if there was any way to get that command DROP SCHEMA
interpret the variable schema_to_drop
with the value that is loaded in it.
The correct way to inform that a reply was useful is not to add a comment saying that it was solved but to mark the answer as accepted tour.
– anonimo