Thread: How to extract information from pg_ddl_command type

The function pg_event_trigger_ddl_commands() returns several columns, one of them being "command" that is of the type
"pg_ddl_command".
 

The manual[1] describes this column as: 

> A complete representation of the command, in internal format. 
> This cannot be output directly, but it can be passed to other 
> functions to obtain different pieces of information about the 
> command.

However, I can not find any of those "other functions" to extract information from that column. 

My goal is to get the complete SQL text that fired the event trigger - is that even possible through
pg_event_trigger_ddl_commands()?
 

Thanks
Thomas

[1] https://www.postgresql.org/docs/current/functions-event-triggers.html

On 2019-Jul-11, Thomas Kellerer wrote:

> The function pg_event_trigger_ddl_commands() returns several columns, one of them being "command" that is of the type
"pg_ddl_command".
 
> 
> The manual[1] describes this column as: 
> 
> > A complete representation of the command, in internal format. 
> > This cannot be output directly, but it can be passed to other 
> > functions to obtain different pieces of information about the 
> > command.
> 
> However, I can not find any of those "other functions" to extract information from that column. 
> 
> My goal is to get the complete SQL text that fired the event trigger - is that even possible through
pg_event_trigger_ddl_commands()?
 

You need a C function.  See the test module in
src/test/modules/test_ddl_deparse -- it contains a very simple
implementation that doesn't really do a lot, but maybe it helps.  The
real meat of the thing is in an outdated module I wrote for 9.5.  I
think the latest version of that was posted by Alexander Shulgin in 2015
here
https://postgr.es/m/CACACo5Q_UXYwF117LBhjZ3xaMPyrgqnqE=mXvRhEfjJ51aCfwQ@mail.gmail.com
or maybe it's here
https://postgr.es/m/CACACo5QQuAV+n4Gi+YA1JF_a+QenR6SJuP8CYdPSrXKa+FHS3A@mail.gmail.com

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