Re: Adding the optional clause 'AS' in CREATE TRIGGER

"Okano, Naoki" <okano(dot)naoki(at)jp(dot)fujitsu(dot)com> writes:
> I would like to add the following support for a trigger.
> This implementation enables to create a trigger efficiently
> in single command.

> It had been discussed before. The link is as shown below.
> https://www.postgresql.org/message-id/CAA-aLv4m%3Df9cc1zcUzM49pE8%2B2NpytUDraTgfBmkTOkMN_wO2w%40mail.gmail.com

I think people pretty much lost interest in that proposal, which is
why it never got finished. Aside from the definitional issues discussed
in that thread, I can think of a few other problems:

1. The impression I have is that most people write trigger functions
so that they can be shared across multiple uses. That'd be impossible
with anonymous trigger function blocks. So you'd be trading off merging
two commands into one, versus having to write out the whole body of the
trigger multiple times, which wouldn't be much of a win.

2. I am concerned that every time we add any syntax to CREATE FUNCTION,
we'll have to think about whether we need to add it to CREATE TRIGGER.
(Or more likely, we'll forget and then users will complain.)

3. There's a lot of infrastructure that's built up around CREATE FUNCTION,
such as psql's \ef and \sf commands. We'd soon find ourselves having to
duplicate all that for triggers.

So personally I feel that the value-for-work-expended ratio here is
pretty low.

But in any case it would be a serious mistake to do this without first
implementing CREATE OR REPLACE TRIGGER. I think that's an entirely
separate proposal and you would be well advised to treat it as such.
It's far from trivial because of locking considerations. You probably
have to take an exclusive lock on the owning relation in order to change
trigger properties. (An advantage of the separate-function-definition
approach is that you can replace the function body with a much weaker
lock.)

> Supporting 'AS' clause in CREATE TRIGGER syntax will enable the option of
> defining the trigger in single command.
> As a bonus, it will be compatible with oracle.

It certainly would not match Oracle's syntax for trigger bodies. It might
slightly reduce the conversion pain, but only slightly.

regards, tom lane