Postgres Pro
Downloads
Home   >   mailing lists

Thread: Shorthand syntax for triggers

Shorthand syntax for triggers

From
Joe Van Dyk
Date:
Instead of this:

create function some_trigger() returns trigger as $$ 
begin                                          
if TG_OP = 'DELETE' then                                                                            
  insert into audits values (OLD.value);                                                            
else                                                                                                
  insert into audits values (NEW.value);                                                            
end if;                                                                                             
return NULL;                                                                                        
end 
$$ language plpgsql;                 
                                                                                                    
create trigger some_trigger after insert on products                                                
for each row execute procedure some_trigger();


I wish I could do:

create trigger some_trigger after insert on products
execute procedure do $$ begin
insert into audits values (CHANGED.value); 
end $$ language plpgsql;


Changes/improvements:

1. Triggers default to 'for each row'

2. Triggers can use anonymous functions

3. Triggers can access a special CHANGED value that's either NEW for insert or updates, or OLD for deletes.

4. Default for 'after insert' triggers is to return null, as I believe it doesn't matter what you return here.

5. Way less repetitive typing.


Thoughts? Is this a terrible idea?

Re: Shorthand syntax for triggers

From
Sergey Konoplev
Date:
On Wed, Oct 10, 2012 at 9:22 AM, Joe Van Dyk <joe@tanga.com> wrote:
> I wish I could do:
>
> create trigger some_trigger after insert on products
> execute procedure do $$ begin
> insert into audits values (CHANGED.value);
> end $$ language plpgsql;

IF TG_OP = 'DELETE' THEN RENAME OLD TO myrow;
ELSE RENAME NEW TO myrow; END IF;

and then use

insert into audits values (myrow.value);

>
>
> Changes/improvements:
>
> 1. Triggers default to 'for each row'
>
> 2. Triggers can use anonymous functions
>
> 3. Triggers can access a special CHANGED value that's either NEW for insert
> or updates, or OLD for deletes.
>
> 4. Default for 'after insert' triggers is to return null, as I believe it
> doesn't matter what you return here.
>
> 5. Way less repetitive typing.
>
>
> Thoughts? Is this a terrible idea?



--
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +14158679984

Re: Shorthand syntax for triggers

From
Craig Ringer
Date:
On 10/11/2012 12:22 AM, Joe Van Dyk wrote:

> 3. Triggers can access a special CHANGED value that's either NEW for
> insert or updates, or OLD for deletes.

I'm not a big fan of the prior comments about small syntax changes, but
this would simplify quite a bit of code. I'd *really* like a way to
refer to "NEW for INSERT or UPDATE, OLD for DELETE" as an implicit
automatic variable.

I do see the appeal of making trigger functions anonymous, but I really
doubt it's worth the hassle.

> 4. Default for 'after insert' triggers is to return null, as I believe
> it doesn't matter what you return here.

This is a trivial convenience, but not one I'd be against.

> 5. Way less repetitive typing.

If you're repeating the same triggers over and over you may want to look
at writing them to be re-usable. See eg:

     http://wiki.postgresql.org/wiki/Audit_trigger_91plus

--
Craig Ringer

Re: Shorthand syntax for triggers

From
Moshe Jacobson
Date:
On Wed, Oct 10, 2012 at 4:28 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
> I wish I could do:
>
> create trigger some_trigger after insert on products
> execute procedure do $$ begin
> insert into audits values (CHANGED.value);
> end $$ language plpgsql;

IF TG_OP = 'DELETE' THEN RENAME OLD TO myrow;
ELSE RENAME NEW TO myrow; END IF;

and then use

insert into audits values (myrow.value);

Does the RENAME syntax actually work? My understanding is that it has been broken for a long time.

--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com

Re: Shorthand syntax for triggers

From
Sergey Konoplev
Date:
On Mon, Oct 15, 2012 at 11:36 AM, Moshe Jacobson <moshe@neadwerx.com> wrote:
>> IF TG_OP = 'DELETE' THEN RENAME OLD TO myrow;
>> ELSE RENAME NEW TO myrow; END IF;
>
> Does the RENAME syntax actually work? My understanding is that it has been
> broken for a long time.

Oh, you got a point! It does not work since 9.0. I forget it, sorry.

Another way to do it is:

DECLARE
    myrow record;
    ...
BEGIN
    myrow := CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
    ...

--
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +14158679984