Thread

Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

Andrew Dunstan <andrew@dunslane.net> — 2025-10-15T19:10:03Z

On 2025-10-14 Tu 5:29 PM, Philip Alger wrote:
> Apologies, I forgot to add a new version of the patch with the 
> documentation change.
>
> This is my first time doing this.
>
>
>         your documentation and the function's comment specifically say
>         that the
>         function take a trigger name and a table name, so it should
>         not use
>         regclass type, which allows OID as input as well.
>
>
>     Thanks for pointing that out in the documentation.
>
>     I think the regclass type actually makes it easier to use because
>     you can input a name or OID, but you're right in that it is
>     essentially 'table_name'::regclass::oid. However, isn't the point
>     of the regclass type to make it easier to do table lookups? In
>     that case, using a name seems easier.
>

I think you should change the documentation. It seems better to use the 
regfoo types where available to save a lot a code duplication.


>
>         There is already a family of pg_get_[xxx]def functions
>         available in
>         PostgreSQL. pg_get_triggerdef() being one of them and it
>         already can take
>         OID as input and output the same text, so regclass type is not
>         necessary.
>
>
>     True, but you have to look for the trigger OID. If you have more
>     than one table using the same trigger name, then you have to
>     figure that out as well. Using names over OIDs seems more
>     intuitive and less error prone than having to look up all the OIDs
>     in my opinion. Also, having the added option of using an OID as
>     well shouldn't be frowned upon since that's what it's using under
>     the hood with regclass, but I understand what you're saying about
>     pg_get_triggerdef(OID) doing the same thing with the OID only.
>

Yes, what this function buys us anything is that you don't need to get 
the trigger OID.


>         The term "DDL statement" may be a little misleading here, it
>         does not return
>         the actual DDL statements executed to create the trigger. The
>         documentation for
>         "pg_get_triggerdef" calls this statement as follows :
>
>         "the creating command for a trigger. (This is a decompiled
>         reconstruction,
>         not the original text of the command.)"
>
>
>     True, Cary. Appreciate calling that out. I can fix that in the
>     documentation as well.
>

by "DDL statement" we mean a statement that would create the object as 
it exists now if it were not already present, not the original creation 
statement. I don't think we need to state that all over the place.


cheers


andrew


--
Andrew Dunstan
EDB:https://www.enterprisedb.com