Обсуждение: AW: AW: [HACKERS] Rule system

Поиск
Список
Период
Сортировка

AW: AW: [HACKERS] Rule system

От
Andreas Zeugswetter
Дата:
>>    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 !

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.

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


Re: AW: AW: [HACKERS] Rule system

От
jwieck@debis.com (Jan Wieck)
Дата:
>
> >>    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) #

Re: AW: AW: [HACKERS] Rule system

От
Vadim Mikheev
Дата:
Jan Wieck wrote:
>
>     It might look like:
>
>         create trigger mytrig before insert or update on mytab
>             for each row do (
                           ^^
Why not EXECUTE ?

>                 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.
      ^^^^^^^^^^^^^^^^^
I missed here. What did you mean?

Vadim
P.S. Sorry, I'm very busy currently :((

Re: AW: AW: [HACKERS] Rule system

От
jwieck@debis.com (Jan Wieck)
Дата:
>
> Jan Wieck wrote:
> >
> >     It might look like:
> >
> >         create trigger mytrig before insert or update on mytab
> >             for each row do (
>                            ^^
> Why not EXECUTE ?

    Just  to indicate that this time a function body, for which a
    trigger function has  to  be  created  on  the  fly,  follows
    instead of the name of an existing function to be called.

    But  for  bison  it  should  be  no problem to decide whether
    EXECUTE PROCEDURE proname(args) or EXECUTE  PROCEDURE  (body)
    is used.  I don't really care about the final syntax.

>
> >                 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.
>       ^^^^^^^^^^^^^^^^^
> I missed here. What did you mean?

    What I have in mind for

        CREATE TRIGGER ... EXECUTE PROCEDURE (body)

    is,  that  this  time  a  trigger  function  is automatically
    created before the usual trigger is defined. For the function
    there  is  a  name  required.   Currently  the  following  is
    possible:

        CREATE TRIGGER on_insert AFTER INSERT on emp
            FOR EACH ROW EXECUTE PROCEDURE on_ins_emp();

        CREATE TRIGGER on_insert AFTER INSERT on payroll
            FOR EACH ROW EXECUTE PROCEDURE on_ins_payroll();

    The name of the trigger  is  the  same  but  the  table  they
    triggered  for  differs  and they call different functions. I
    don't want to loose this so we cannot use  the  name  of  the
    trigger  (on_insert)  to  create  the  trigger function since
    overloading depends on different  call  arguments.  But  both
    functions have no call arguments.

    Clear now?

>
> Vadim
> P.S. Sorry, I'm very busy currently :((
>
>


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) #