Re: AW: [HACKERS] Rule system

>> Jan Wieck wrote:
>> > What else must be there? I think everything on the instance
>> > level is better done by triggers. And if we add
>> > row-/statement-level triggers on SELECT, there would be no
>> > reason left to have non-instead rules. Or am I missing
>> > something?
>> While this is in my opinion true, it would be nice to extend the trigger syntax to
>> allow the triggered action to be expressed in sql like:
>>
>> create trigger blabla after delete on people
>> referencing old as o
>> (insert into graves values (o.*)); -- disregard the syntax
>>
>> Andreas
>
> With PL/pgSQL I can actually do the following:

> create function on_death() returns opaque as '
> begin
> insert into graves (name, born, died)
> values (old.name, old.born, ''now'');
> return old;
> end;
> ' language 'plpgsql';
>
> create trigger on_death after delete on people
> for each row execute procedure on_death();
>
> I think we could extend the parser that it accepts the above
> syntax and internally creates the required trigger procedure
> and the trigger itself in the way we treat triggers now. This
> is the same way we actually deal with views (accept create
> view but do create table and create rule internally).
yup, that would be nice
>
> It would require two extensions to PL/pgSQL:
>
> A 'RENAME oldname newname' in the declarations part so
> the internal trigger procedures record 'old' can be
> renamed to 'o'.

Actually, since this does not give added functionality, I guess always using the
keywords old and new would be ok (get rid of "current" though, it is unclear and has
another SQL92 meaning).

>
> Implementation of referencing record/rowtype.* extends to
> a comma separated list of parameters when manipulating
> the insert statement. My current implementation of
> PL/pgSQL can only substitute a single
> variable/recordfiled/rowfield into one parameter.

This is a feature, that would make life easier ;-) (low priority)

The real problem I have would be procedures that return more than one column (or an opaque row/or rows),
or nothing at all.
Like:

create function onename returns char(16), char(16) -- or would it have to be returns opaque ?
as 'select "Hans", "Moser"' language 'sql';

insert into employee (fname, lname) values (onename()); -- or
insert into employee (fname, lname) select onename();
>
> These two wouldn't be that complicated. And it would have a
> real advantage. As you see above, I must double any ' because
> the function body is written inside of ''s. It's a pain - and
> here's a solution to get out of it.

That is why I suggested a while ago to keyword begin and end for plpgsql,
then everything between begin and end would be plsql automatically without the quotes.
This would then be much like Oracle PL/SQL.

Something like:
create function delrow (int highestsalary) as begin
delete from employee where sal > highestsalary; -- or :highestsalary or $highestsalary
end;

>
> If anyone is happy with this, I would release PL/pgSQL after
> 6.4 and make the required changes in the parser.
>

Actually for me the possibility to return an opaque row from a function
would currently be the most important enhancement of all.
Somewhere the code that handles the "returns opaque" case is missing code to
handle the case where a whole tuple is returned.

Andreas