Triggers with DO functionality

[Thom Brown-2]

Hi,

This may have already been discussed before, but I can't find any
mention of it.  Would it be desirable to add support for triggers that
contain their own anonymous functions (i.e. DO)?

So instead of

CREATE TRIGGER...
EXECUTE PROCEDURE functioname();

you'd have:

CREATE TRIGGER...
DO $$
...
$$;

The purpose being to only have a single statement to set up the
trigger rather than setting up a separate trigger function which will
unlikely be re-used by other triggers... or is this of dubious
benefit?

--
Thom

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David E. Wheeler-3]

On Feb 17, 2012, at 5:22 AM, Thom Brown wrote:

> The purpose being to only have a single statement to set up the
> trigger rather than setting up a separate trigger function which will
> unlikely be re-used by other triggers... or is this of dubious
> benefit?

+1, though I imagine it would just give it a generated name and save it anyway, eh?

David


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Andrew Dunstan]

On 02/17/2012 11:29 AM, David E. Wheeler wrote:
> On Feb 17, 2012, at 5:22 AM, Thom Brown wrote:
>
>> The purpose being to only have a single statement to set up the
>> trigger rather than setting up a separate trigger function which will
>> unlikely be re-used by other triggers... or is this of dubious
>> benefit?
> +1, though I imagine it would just give it a generated name and save it anyway, eh?
>



Before we rush into this, let's consider all the wrinkles. For example,
what if you need to change the function? And how would you edit the
function in psql? It might be a bit more involved that it seems at first
glance, although my initial reaction was the same as David's.

cheers

andrew

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Thom Brown-2]

On 17 February 2012 16:29, David E. Wheeler <[hidden email]> wrote:
> On Feb 17, 2012, at 5:22 AM, Thom Brown wrote:
>
>> The purpose being to only have a single statement to set up the
>> trigger rather than setting up a separate trigger function which will
>> unlikely be re-used by other triggers... or is this of dubious
>> benefit?
>
> +1, though I imagine it would just give it a generated name and save it anyway, eh?

I had thought about that, yes, but I didn't want to get bogged down in
implementation.

--
Thom

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Thom Brown-2]

On 17 February 2012 16:43, Andrew Dunstan <[hidden email]> wrote:

>
>
> On 02/17/2012 11:29 AM, David E. Wheeler wrote:
>>
>> On Feb 17, 2012, at 5:22 AM, Thom Brown wrote:
>>
>>> The purpose being to only have a single statement to set up the
>>> trigger rather than setting up a separate trigger function which will
>>> unlikely be re-used by other triggers... or is this of dubious
>>> benefit?
>>
>> +1, though I imagine it would just give it a generated name and save it
>> anyway, eh?
>>
>
>
>
> Before we rush into this, let's consider all the wrinkles. For example, what
> if you need to change the function? And how would you edit the function in
> psql? It might be a bit more involved that it seems at first glance,
> although my initial reaction was the same as David's.

Why not just...

CREATE OR REPLACE TRIGGER my_trigger...

--
Thom

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Andrew Dunstan]

On 02/17/2012 11:46 AM, Thom Brown wrote:

> On 17 February 2012 16:43, Andrew Dunstan<[hidden email]>  wrote:
>>
>> On 02/17/2012 11:29 AM, David E. Wheeler wrote:
>>> On Feb 17, 2012, at 5:22 AM, Thom Brown wrote:
>>>
>>>> The purpose being to only have a single statement to set up the
>>>> trigger rather than setting up a separate trigger function which will
>>>> unlikely be re-used by other triggers... or is this of dubious
>>>> benefit?
>>> +1, though I imagine it would just give it a generated name and save it
>>> anyway, eh?
>>>
>>
>>
>> Before we rush into this, let's consider all the wrinkles. For example, what
>> if you need to change the function? And how would you edit the function in
>> psql? It might be a bit more involved that it seems at first glance,
>> although my initial reaction was the same as David's.
> Why not just...
>
> CREATE OR REPLACE TRIGGER my_trigger...
>

Maybe that would do it. You might also want a \e command for psql to
match it.

cheers

andrew

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Peter Eisentraut-2]

On fre, 2012-02-17 at 13:22 +0000, Thom Brown wrote:

> So instead of
>
> CREATE TRIGGER...
> EXECUTE PROCEDURE functioname();
>
> you'd have:
>
> CREATE TRIGGER...
> DO $$
> ...
> $$;

I had wished for this many times and was about to propose something
similar.

We might wish to review the SQL standard and other implementations to
make porting triggers a bit easier too.

Also, whatever ALTER functionality functions have would have to be made
available here as well.


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Thom Brown-2]

On 17 February 2012 17:26, Peter Eisentraut <[hidden email]> wrote:

> On fre, 2012-02-17 at 13:22 +0000, Thom Brown wrote:
>> So instead of
>>
>> CREATE TRIGGER...
>> EXECUTE PROCEDURE functioname();
>>
>> you'd have:
>>
>> CREATE TRIGGER...
>> DO $$
>> ...
>> $$;
>
> I had wished for this many times and was about to propose something
> similar.
>
> We might wish to review the SQL standard and other implementations to
> make porting triggers a bit easier too.

I had looked at how a couple other RDBMS's do it, and there are:

CREATE TRIGGER...
BEGIN
END;

and

CREATE TRIGGER...
AS
BEGIN
END;

And thinking about it, DO is a bit nonsense here, so maybe we'd just
have something like:

CREATE TRIGGER...
AS $$
BEGIN
END;
$$;

i.e. the same as a function.

--
Thom

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Dimitri Fontaine-7]

Thom Brown <[hidden email]> writes:

> And thinking about it, DO is a bit nonsense here, so maybe we'd just
> have something like:
>
> CREATE TRIGGER...
> AS $$
> BEGIN
> END;
> $$;
>
> i.e. the same as a function.

I like that.  How do you tell which language the trigger is written in?
I'm not so sure about other function properties (SET, COST, ROWS,
SECURITY DEFINER etc) because applying default and punting users to go
use the full CREATE FUNCTION syntax would be a practical answer here.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Thom Brown-2]

On 17 February 2012 20:40, Dimitri Fontaine <[hidden email]> wrote:

> Thom Brown <[hidden email]> writes:
>> And thinking about it, DO is a bit nonsense here, so maybe we'd just
>> have something like:
>>
>> CREATE TRIGGER...
>> AS $$
>> BEGIN
>> END;
>> $$;
>>
>> i.e. the same as a function.
>
> I like that.  How do you tell which language the trigger is written in?

Exactly the same as a function I'd imagine.  Just tack LANGUAGE
<language>; at the end.

> I'm not so sure about other function properties (SET, COST, ROWS,
> SECURITY DEFINER etc) because applying default and punting users to go
> use the full CREATE FUNCTION syntax would be a practical answer here.

*shrug* There's also the question about the stability of the trigger's
own in-line function too (i.e. IMMUTABLE, STABLE, VOLATILE).

--
Thom

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Andrew Dunstan]

On 02/17/2012 03:58 PM, Thom Brown wrote:

> On 17 February 2012 20:40, Dimitri Fontaine<[hidden email]>  wrote:
>> Thom Brown<[hidden email]>  writes:
>>> And thinking about it, DO is a bit nonsense here, so maybe we'd just
>>> have something like:
>>>
>>> CREATE TRIGGER...
>>> AS $$
>>> BEGIN
>>> END;
>>> $$;
>>>
>>> i.e. the same as a function.
>> I like that.  How do you tell which language the trigger is written in?
> Exactly the same as a function I'd imagine.  Just tack LANGUAGE
> <language>; at the end.
>
>> I'm not so sure about other function properties (SET, COST, ROWS,
>> SECURITY DEFINER etc) because applying default and punting users to go
>> use the full CREATE FUNCTION syntax would be a practical answer here.
> *shrug* There's also the question about the stability of the trigger's
> own in-line function too (i.e. IMMUTABLE, STABLE, VOLATILE).
>

This is going to be pretty much a piece of syntactic sugar. Would it
matter that much if the trigger functions made thus are all volatile? If
someone wants the full function feature set they can always use CREATE
FUNCTION first. I think I'm with Dimitri - let's keep it simple.

cheers

andrew

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Thom Brown-2]

On 17 February 2012 21:07, Andrew Dunstan <[hidden email]> wrote:

>
>
> On 02/17/2012 03:58 PM, Thom Brown wrote:
>>
>> On 17 February 2012 20:40, Dimitri Fontaine<[hidden email]>
>>  wrote:
>>>
>>> Thom Brown<[hidden email]>  writes:
>>>>
>>>> And thinking about it, DO is a bit nonsense here, so maybe we'd just
>>>> have something like:
>>>>
>>>> CREATE TRIGGER...
>>>> AS $$
>>>> BEGIN
>>>> END;
>>>> $$;
>>>>
>>>> i.e. the same as a function.
>>>
>>> I like that.  How do you tell which language the trigger is written in?
>>
>> Exactly the same as a function I'd imagine.  Just tack LANGUAGE
>> <language>; at the end.
>>
>>> I'm not so sure about other function properties (SET, COST, ROWS,
>>> SECURITY DEFINER etc) because applying default and punting users to go
>>> use the full CREATE FUNCTION syntax would be a practical answer here.
>>
>> *shrug* There's also the question about the stability of the trigger's
>> own in-line function too (i.e. IMMUTABLE, STABLE, VOLATILE).
>>
>
> This is going to be pretty much a piece of syntactic sugar. Would it matter
> that much if the trigger functions made thus are all volatile? If someone
> wants the full function feature set they can always use CREATE FUNCTION
> first. I think I'm with Dimitri - let's keep it simple.

Yes, always best to start with essential functionality.

--
Thom

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

Andrew Dunstan <[hidden email]> writes:
> On 02/17/2012 03:58 PM, Thom Brown wrote:
>> On 17 February 2012 20:40, Dimitri Fontaine<[hidden email]>  wrote:
>>> I'm not so sure about other function properties (SET, COST, ROWS,
>>> SECURITY DEFINER etc) because applying default and punting users to go
>>> use the full CREATE FUNCTION syntax would be a practical answer here.

> This is going to be pretty much a piece of syntactic sugar. Would it
> matter that much if the trigger functions made thus are all volatile? If
> someone wants the full function feature set they can always use CREATE
> FUNCTION first. I think I'm with Dimitri - let's keep it simple.

Volatility is a complete no-op for a trigger function anyway, as are
other planner parameters such as cost/rows, because there is no planning
involved in trigger calls.

Of the existing CREATE FUNCTION options, I think only LANGUAGE, SECURITY
DEFINER, and SET are of any possible interest for a trigger function.
And I don't have any problem deeming SET a second-order thing that you
should have to go use CREATE FUNCTION for.  But perhaps SECURITY DEFINER
is a common enough need to justify including in this shorthand form.

Has anybody stopped to look at the SQL standard for this?  In-line
trigger definitions are actually what they intend, IIRC.

                        regards, tom lane

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Dimitri Fontaine-7]

Tom Lane <[hidden email]> writes:
> Has anybody stopped to look at the SQL standard for this?  In-line
> trigger definitions are actually what they intend, IIRC.

In which language?  Do we need to include PL/PSM to be compliant, and
use that by default?  In that case we might want to force people to
spell out LANGUAGE plpgsql when we don't provide for PSM yet, so that we
avoid some backwards compatibility problems down the road.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

Dimitri Fontaine <[hidden email]> writes:
> Tom Lane <[hidden email]> writes:
>> Has anybody stopped to look at the SQL standard for this?  In-line
>> trigger definitions are actually what they intend, IIRC.

> In which language?  Do we need to include PL/PSM to be compliant, and
> use that by default?

Darn if I know.  But let's make sure we don't paint ourselves into a
corner such that we couldn't support the standard's syntax sometime
in the future.

> In that case we might want to force people to
> spell out LANGUAGE plpgsql when we don't provide for PSM yet, so that we
> avoid some backwards compatibility problems down the road.

I suspect that we can avoid that as long as the command is based around
a string literal for the function body.  OTOH, CREATE FUNCTION has never
had a default for LANGUAGE, and we don't get many complaints about that,
so maybe insisting that LANGUAGE be supplied for an in-line trigger
isn't unreasonable.

                        regards, tom lane

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Jaime Casanova-3]

On Fri, Feb 17, 2012 at 4:46 PM, Tom Lane <[hidden email]> wrote:
>
> Has anybody stopped to look at the SQL standard for this?  In-line
> trigger definitions are actually what they intend, IIRC.
>

this is what i found there

<trigger definition> ::=
  CREATE TRIGGER <trigger name> <trigger action time> <trigger event>
      ON <table name> [ REFERENCING <transition table or variable list> ]
      <triggered action>

<triggered action> ::=
  [ FOR EACH { ROW | STATEMENT } ]
      [ WHEN <left paren> <search condition> <right paren> ]
      <triggered SQL statement>

<triggered SQL statement> ::=
    <SQL procedure statement>
  | BEGIN ATOMIC { <SQL procedure statement> <semicolon> }... END

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Gianni Ciolli]

On Fri, Feb 17, 2012 at 11:43:53AM -0500, Andrew Dunstan wrote:

> On 02/17/2012 11:29 AM, David E. Wheeler wrote:
> >On Feb 17, 2012, at 5:22 AM, Thom Brown wrote:
> >>The purpose being to only have a single statement to set up the
> >>trigger rather than setting up a separate trigger function which will
> >>unlikely be re-used by other triggers... or is this of dubious
> >>benefit?
> >+1, though I imagine it would just give it a generated name and save it anyway, eh?
> Before we rush into this, let's consider all the wrinkles. For
> example, what if you need to change the function? And how would you
> edit the function in psql? It might be a bit more involved that it
> seems at first glance, although my initial reaction was the same as
> David's.

Another complication: anonymous triggers would either have to be
alone, or provide a mechanism to manage a sequence of anonymous
triggers on the same table (such as "replace the third trigger with
..." or "move trigger #4 in position #2", or deciding their order of
execution).

Cheers,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
[hidden email] | www.2ndquadrant.it

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Thom Brown-2]

On 23 February 2012 07:15, Gianni Ciolli <[hidden email]> wrote:

> On Fri, Feb 17, 2012 at 11:43:53AM -0500, Andrew Dunstan wrote:
>> On 02/17/2012 11:29 AM, David E. Wheeler wrote:
>> >On Feb 17, 2012, at 5:22 AM, Thom Brown wrote:
>> >>The purpose being to only have a single statement to set up the
>> >>trigger rather than setting up a separate trigger function which will
>> >>unlikely be re-used by other triggers... or is this of dubious
>> >>benefit?
>> >+1, though I imagine it would just give it a generated name and save it anyway, eh?
>> Before we rush into this, let's consider all the wrinkles. For
>> example, what if you need to change the function? And how would you
>> edit the function in psql? It might be a bit more involved that it
>> seems at first glance, although my initial reaction was the same as
>> David's.
>
> Another complication: anonymous triggers would either have to be
> alone, or provide a mechanism to manage a sequence of anonymous
> triggers on the same table (such as "replace the third trigger with
> ..." or "move trigger #4 in position #2", or deciding their order of
> execution).

Isn't the order of execution alphabetical by trigger name in
PostgreSQL?  The Triggers themselves wouldn't be anonymous, we'd still
be naming them.  It's the referenced functions that would no longer
need defining, and even those probably won't technically be anonymous
as they'll need cataloguing somewhere.

--
Thom

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Gianni Ciolli]

On Thu, Feb 23, 2012 at 08:26:47AM +0000, Thom Brown wrote:

> On 23 February 2012 07:15, Gianni Ciolli <[hidden email]> wrote:
> > Another complication: anonymous triggers would either have to be
> > alone, or provide a mechanism to manage a sequence of anonymous
> > triggers on the same table (such as "replace the third trigger with
> > ..." or "move trigger #4 in position #2", or deciding their order of
> > execution).
>
> Isn't the order of execution alphabetical by trigger name in
> PostgreSQL?  The Triggers themselves wouldn't be anonymous, we'd still
> be naming them.  It's the referenced functions that would no longer
> need defining, and even those probably won't technically be anonymous
> as they'll need cataloguing somewhere.

You're right, sorry.

I misread the proposal as "anonymous triggers" when instead it is
"(named) triggers each implemented via an anonymous function".

Cheers,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
[hidden email] | www.2ndquadrant.it

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Peter Eisentraut-2]

On fre, 2012-02-17 at 16:46 -0500, Tom Lane wrote:
> But perhaps SECURITY DEFINER is a common enough need to justify
> including in this shorthand form.

According to the SQL standard, trigger actions run in security definer
mode.  I would hope that we could go with that by default for inline
trigger actions, because it's the thing that makes sense for triggers
most of the time anyway, I think.



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers