DROP TRIGGER using variable to define trigger's name

Question

I have two environments, each of them have different trigger's name and I want to use the same script on both envs to drop a trigger I have...

The problem is, when I do as follows:

variable := 'trigger_name';

DROP TRIGGER variable ON my_table;

it tries to DROP a trigger named 'variable' not 'trigger_name' as I expected... How can I do such thing?

Thank you in advance! -B.J.

Craig Ringer · Accepted Answer · 2014-09-18 14:29:16Z

3

You must use dynamic SQL with EXECUTE. You're already using PL/PgSQL, so just:

variable := 'trigger_name';

EXECUTE format('DROP TRIGGER %I ON my_table', variable);

answered Sep 18, 2014 at 14:29

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1