Postgresql, Variables for psql functions

Asked

Viewed 857 times

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.

2 answers

2


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

0

Thank you Camilo Santos for the reply.

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

  • 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.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.