Postgresql, Variables for psql functions

Question

Postgresql, Variables for psql functions

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I'm trying to create a trigger function to remove a schema created in the database based on an idproj attribute of 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 . So I would like to remove the corresponding schema by deleting the record in the geo.projetos table.

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;

Creating the 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 get 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 like the problem is handling the schema_to_drop variable that is not replaced with proj_87 .

I wonder if there is any way to get the DROP SCHEMA command to interpret the variable schema_to_drop with the value that is loaded in it.

postgresql psql

asked by anonymous 11.01.2018 / 20:35

2 answers

How to create a folder browser in java swing? SQL query for Laravel

score 0 · Answer 1

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

The EXECUTE command runs the SQL command entered in the string. I used the format function to allow the schema_to_drop variable to be used instead of %I . The format understands that where there is %I there will be a name substitution 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;

link

score 0 · Answer 2

0

Thanks Camilo Santos for the answer.

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

14.01.2018 / 21:35