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

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