On 14 January 2016 at 12:08, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 01/13/2016 02:51 PM, David Rowley wrote:
On 14 January 2016 at 11:32, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
On 01/13/2016 02:24 PM, Tom Lane wrote:
"Williamson, Michael" <Michael.Williamson@xxxxxxxxxx<mailto:Michael.Williamson@xxxxxxxxxx>> writes:
I'm attempting to drop a trigger that may or may not exist,
so am using
the "IF EXISTS" clause. Â This works fine for tables, views,
functions,
domains, and types, but for some reason seems to be ignored for
triggers. Â I'd expect to see more about this online if it
were a bug,
so I'm thinking I may be missing something obvious.
Example:
DROP TRIGGER IF EXISTS udf_customer_update_trigger ON customer;
Expected Output:
NOTICE:Â Â trigger "udf_customer_update_trigger" does not
exist, skipping
Observed Output:
ERROR:Â Â relation "udf_customer_update_trigger" does not exist
Environment:
CentOS 6.6
postgresql91-server-9.1.14-1PGDG.rhel6.x86_64
This has worked the way you're imagining since (I think) 9.4.
Before
that the "if exists" semantics only applied to the trigger itself,
not to the relation.
Alright now I am confused. Other then changing table to table_name I
am not seeing where the below changed. In both cases a NOTICE is
supposed to be raised.
http://www.postgresql.org/docs/9.1/interactive/sql-droptrigger.html
http://www.postgresql.org/docs/9.4/interactive/sql-droptrigger.html
Seems to have been changed in
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b152c6cd0de1827ba58756e24e18110cf902182a
I will take a look at this at some point. The part that has me confused from the original post is this:
ERROR: relation "udf_customer_update_trigger" does not exist
If the ERROR is because the table does not exist, why not?:
ERROR: relation "customer" does not exist
I assumed this part was a mistake in the post by Michael. I tried this on 9.1.19 and I correctly get the name of the table rather than the name of the trigger. So even in the unlikely event that this was a bug, it's working in the latest 9.1 minor release:
# drop trigger if exists test_trigger on testtable;
ERROR: relation "testtable" does not exist
# select version();
version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.19 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 5.2.1-22 ubuntu2) 5.2.1 20151010, 64-bit
(1 row)
