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

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