EN VI

Postgresql - current_setting with -v option?

How to Postgresql - current_setting with -v option

I'm trying to call this sql file that drop triggers with a certain name :

DO $$
DECLARE
    r RECORD;
    v_trig_name text := current_setting('trig_name', true); -- Récupération de la valeur de la variable
BEGIN
    FOR r IN (SELECT event_object_table, trigger_name
              FROM information_schema.triggers
              WHERE trigger_name=v_trig_name)
    LOOP
        EXECUTE 'DROP TRIGGER IF EXISTS ' || quote_ident(r.trigger_name) ||
                ' ON ' || quote_ident(r.event_object_table) || ';';
    END LOOP;
END $$;

With this psql command :

psql -d dbtest -f /var/lib/postgresql/data/dump/drop_triggers.sql -U srhbatch -v trig_name='trigger_test'

But when I get a liste of my db triggers, 'trigger_test' is still here. Moreover, when I remove the 'true' option from current_setting, I get an error message like this :

ERROR:  unrecognized configuration parameter "trig_name"

I use the -v option on other sql files, but with this one I can't make it work. Maybe it is related to the fact that I use it with plpgsql ? I someone have any advice ?

Solution:

A DO statement is a string literal, and you cannot substitute a psql variable inside a string literal. Moreover, psql variables are not read with current_setting() — that is for configuration parameters. psql variables are accessed with a : prefix.

With psql, the simplest solution is to use \gexec:

SELECT format(
          'DROP TRIGGER %I ON %I.%I',
          trigger_name,
          event_object_schema,
          event_object_table
       )
FROM information_schema.triggers
WHERE trigger_name = :'v_trig_name'
\gexec

\gexec is used instead of ; and executes every result row as an SQL statement.