Re: AW: AW: [HACKERS] Rule system - Mailing list pgsql-hackers

>
> >>    But  then again, even if functions stay that restricted, what
> >>    do we need as rule functionality?  Up to now I only have  all
> >>    kinds of INSTEAD rules on the statement level on my list.
> >
> >The [select] trigger can't return sets/tuples or multiple rows the select rule system can.
> >This is because of the currently restricted "create function" return values.
> >
> >I'll try to look over my diploma paper tonight, to look for rules that (at least currently)
> >cannot be written as triggers (other than instead rules).
>
> Ok, I did not find anything (I tried hard) :-). Especially nothing that would currently work.
> (I know more of it did work in postgres 4.2 though :-( )
> So I really think the insert/update/delete rules, other than the instead stuff of course, are oblivious,
> and don't work properly anyways, so we could probably really nuke them.
> Not the select rules of course !

    Now  the  target  is  clear. Make sure all instead rules work
    correct and get rid of the others.

    For the triggers: the triggers on SELECT should not  be  able
    to  fire  in additional tuples. I think it would be enough if
    they can modify  the  actual  tuple  before  it  is  used  or
    suppress it at all.

>
> I still think the trigger syntax should be extended to allow a block of sql, like in Informix.
> Then you could: execute one or more procedures, or as in most cases
> do a simple statement like cascade a delete. Also a syntax would be nice
> that would allow to change the "new" tuple.

    It might look like:

        create trigger mytrig before insert or update on mytab
            for each row do (
                begin
                    new.lastupdate := 'now';
                    return new;
                end;
            ) language 'plpgsql';

    This  would be easy. Just an enhancement to the parser and to
    the create trigger  utility  processing  so  it  creates  the
    required  function  on  the fly. Modification of new, raising
    errors via elog() and suppressing  the  operation  itself  by
    returning  NULL  is  already there in PL/pgSQL. We would need
    something smart for the functions  name,  because  using  the
    trigger  name  only  would  break  the current possibility to
    define  the  same  trigger  name  on  different  tables  with
    different actions. Something like __trig_<oid> would be good.

    You would still be able to create a regular function with  no
    arguments  and  return  type  opaque and then create triggers
    with ... for each row execute procedure myothertrig().  There
    can be any number of triggers for the same/overlapping events
    on a table (not on a view - they would never be fired).  This
    is how I currently create triggers in PL/pgSQL.

>
> In Informix the block begins with a ( and ends with ), the statements are separated by commas:
>
> (insert into log values ('insert', new.name),
> execute procedure current_datetime() into new.lastupdate)
>
> While I dont particularly like the syntax it does provide excellent functionality.
>
> Andreas
>

    I  like  the  ()'s  around the statement block. It is already
    something psql cares for when  typing  in  queries.  Anything
    between can have ;'s and ''s as required. I would like to add
    the () to CREATE FUNCTION too.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #