Thread: AW: AW: [HACKERS] Rule system
>> 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
>
> >> 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) #
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 :((
>
> 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) #