Postgresql, Variables for psql functions

Question

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.

Answer 1

Use the EXECUTE, with it you can generate SQL code dynamically in plpgsql.

The command EXECUTE runs the SQL command entered in the string. I used the function format to allow the variable to be used schema_to_drop replacing the %I. The format understands that where there is a %I will be replaced by a name of a database object.

CREATE OR REPLACE FUNCTION geo.drop_schema_projeto_id()
RETURNS trigger as $dsp$
    DECLARE schema_to_drop text := 'proj_' || OLD.idproj::text;
    BEGIN
        EXECUTE format('DROP SCHEMA IF EXISTS %I CASCADE;',schema_to_drop);
        RAISE NOTICE 'Schema a ser removido: %', schema_to_drop;
        RETURN OLD;
    END;
$dsp$ LANGUAGE plpgsql;

https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Answer 2

Thank you Camilo Santos for the reply.

It worked perfectly. Thanks also for the link sent. So I consider the question [SOLVED]