Triggers with DO functionality

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

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

On 17 February 2012 16:29, David E. Wheeler <david@justatheory.com> 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

On 17 February 2012 16:43, Andrew Dunstan <andrew@dunslane.net> 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

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

On 17 February 2012 16:43, Andrew Dunstan<andrew@dunslane.net> 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

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.

On 17 February 2012 17:26, Peter Eisentraut <peter_e@gmx.net> 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

Thom Brown <thom@linux.com> 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

On 17 February 2012 20:40, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:

Thom Brown <thom@linux.com> 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

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

On 17 February 2012 20:40, Dimitri Fontaine<dimitri@2ndquadrant.fr> wrote:

Thom Brown<thom@linux.com> 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

On 17 February 2012 21:07, Andrew Dunstan <andrew@dunslane.net> wrote:

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

On 17 February 2012 20:40, Dimitri Fontaine<dimitri@2ndquadrant.fr>
 wrote:

Thom Brown<thom@linux.com>  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

Andrew Dunstan <andrew@dunslane.net> writes:

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

On 17 February 2012 20:40, Dimitri Fontaine<dimitri@2ndquadrant.fr> 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

Tom Lane <tgl@sss.pgh.pa.us> 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

Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:

Tom Lane <tgl@sss.pgh.pa.us> 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

On Fri, Feb 17, 2012 at 4:46 PM, Tom Lane <tgl@sss.pgh.pa.us> 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

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
gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it

On 23 February 2012 07:15, Gianni Ciolli <gianni.ciolli@2ndquadrant.it> 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

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

On 23 February 2012 07:15, Gianni Ciolli <gianni.ciolli@2ndquadrant.it> 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
gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it

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.