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

[Philip Alger]

Hi Jian

doc said trigger name can not be schema-qualified,
>> we can not do:
>> CREATE TRIGGER public.modified_a BEFORE UPDATE OF a ON main_table
>> FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE
>> trigger_func('modified_a');
>
>
>
>> + nameList = textToQualifiedNameList(trgName);
>>
>>
I am wondering if adding an error message if someone inserted a schema name
would be advantageous?

nameList = textToQualifiedNameList(trgName);
if (list_length(nameList) != 1)
     ereport(ERROR,
         (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
          errmsg("trigger name cannot be schema qualified")));

select pg_get_trigger_ddl('main_table', 'public.modified_a');

ERROR:  trigger name cannot be schema qualified
STATEMENT:  select pg_get_trigger_ddl('main_table', 'public.modified_a');
ERROR:  trigger name cannot be schema qualified

As of now, it would just drop `public`, or any schema name, from the
trigger name. It would specifically look for the trigger name under the
named relation. If there is a trigger name `modified_a` under `main_table`
it will give you the DDL.

select pg_get_trigger_ddl('main_table', 'public.modified_a');

pg_get_trigger_ddl

---------------------------------------------------------------------------------------------------------------------------------------------------
 CREATE TRIGGER modified_a BEFORE UPDATE OF a ON test.main_table FOR EACH
ROW WHEN ((old.a <> new.a)) EXECUTE FUNCTION trigger_func('modified_a');
(1 row)


-- 
Best,
Phil Alger