Postgres Pro
Downloads
Home   >   mailing lists

Thread: Determining table change in an event trigger

Determining table change in an event trigger

From
Jonathan Rogers
Date:
I am trying to use an event trigger to do something when a column
changes. I can declare an event trigger "ON sql_drop WHEN TAG IN ('ALTER
TABLE')" to get dropped columns. However, I can't figure out any good
way to determine when a column has been added or altered.

I can declare an event trigger "ON ddl_command_end WHEN TAG IN ('ALTER
TABLE')" but that gets unwanted events such as disabling triggers on a
table. Function pg_event_trigger_ddl_commands() returns rows with column
"command" of type "pg_ddl_command" which contains "a complete
representation of the command, in internal format." According to the
docs, this cannot be output directly, but it can be passed to other
functions to obtain different pieces of information about the command.
However, I cannot find any other functions which operate on the type
pg_ddl_command. Am I missing something? Is the documentation lacking?

--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers@emphasys-software.com

Re: Determining table change in an event trigger

From
Alvaro Herrera
Date:
Jonathan Rogers wrote:
> I am trying to use an event trigger to do something when a column
> changes. I can declare an event trigger "ON sql_drop WHEN TAG IN ('ALTER
> TABLE')" to get dropped columns. However, I can't figure out any good
> way to determine when a column has been added or altered.
>
> I can declare an event trigger "ON ddl_command_end WHEN TAG IN ('ALTER
> TABLE')" but that gets unwanted events such as disabling triggers on a
> table. Function pg_event_trigger_ddl_commands() returns rows with column
> "command" of type "pg_ddl_command" which contains "a complete
> representation of the command, in internal format." According to the
> docs, this cannot be output directly, but it can be passed to other
> functions to obtain different pieces of information about the command.
> However, I cannot find any other functions which operate on the type
> pg_ddl_command. Am I missing something? Is the documentation lacking?

Yeah, that type can only be processed by C functions.  You'd need to
write a C function to examine the structure and see whether it matches
what you need.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Determining table change in an event trigger

From
Jonathan Rogers
Date:
On 08/24/2016 12:58 AM, Alvaro Herrera wrote:
> Jonathan Rogers wrote:
>> I am trying to use an event trigger to do something when a column
>> changes. I can declare an event trigger "ON sql_drop WHEN TAG IN ('ALTER
>> TABLE')" to get dropped columns. However, I can't figure out any good
>> way to determine when a column has been added or altered.
>>
>> I can declare an event trigger "ON ddl_command_end WHEN TAG IN ('ALTER
>> TABLE')" but that gets unwanted events such as disabling triggers on a
>> table. Function pg_event_trigger_ddl_commands() returns rows with column
>> "command" of type "pg_ddl_command" which contains "a complete
>> representation of the command, in internal format." According to the
>> docs, this cannot be output directly, but it can be passed to other
>> functions to obtain different pieces of information about the command.
>> However, I cannot find any other functions which operate on the type
>> pg_ddl_command. Am I missing something? Is the documentation lacking?
>
> Yeah, that type can only be processed by C functions.  You'd need to
> write a C function to examine the structure and see whether it matches
> what you need.
>

OK, thanks for the explanation. It seems like the docs should make it
clear that the "other functions" are not included.

--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers@emphasys-software.com