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

On 10/16/25 16:20, Philip Alger wrote:
> pg_get_viewdef() sees it differently (opposite approach)
>
> That's true, and it's pretty strict. However, pg_get_trigger_ddl seems
> more intuitive since it can return the statement whether the trigger is
> quoted or unquoted without the user thinking about adding quotes.

I can see how it can be more practical to not care about double quotes
when using pg_get_trigger_ddl(), but IMHO consistency and predictability
are more important in this particular case. If we do this, users would
need to know where to keep or remove the double quotes when using
functions to describe catalog objects. Another argument **for** my case
is the following example:

postgres=# CREATE SCHEMA "S";
CREATE SCHEMA

postgres=# CREATE TABLE "S"."T" (c int);
CREATE TABLE

postgres=# CREATE FUNCTION "S".trgf()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RETURN NULL;
END; $$;
CREATE FUNCTION

postgres=# CREATE TRIGGER trg BEFORE INSERT ON "S"."T"
FOR EACH STATEMENT EXECUTE PROCEDURE "S".trgf();
CREATE TRIGGER

postgres=# SELECT pg_get_trigger_ddl('"S"."T"','trg');
pg_get_trigger_ddl

---------------------------------------------------------------------------------------------
CREATE TRIGGER trg BEFORE INSERT ON "S"."T" FOR EACH STATEMENT EXECUTE
FUNCTION "S".trgf();
(1 row)

postgres=# SELECT pg_get_trigger_ddl('S.T','trg');
ERROR: relation "s.t" does not exist
LINE 1: SELECT pg_get_trigger_ddl('S.T','trg');

The table parameter expects the double quotes, so it would be a hard
sell to make the trigger name parameter **not accept them** either.

In that light, I tend to think that the approach of pg_get_viewdef()
would be the best way to go, but let's see what the other reviewers have
to say about it.

Best, Jim