Rules and views (was Re: [HACKERS] Rules: 2nd patch) - Mailing list pgsql-hackers

>     Next patch will allow instead  nothing  on  select  rules  to
>     return  no  tuples at all instead of one with all NULL fields
>     (as it is now).

    Ooops  -  though  it would be so easy - but it isn't. Instead
    rules for select are a damned thing. Select rules are applied
    totally different from the others, since the parsetree cannot
    get splitted.  Anything I tried resulted  in  something  that
    doesn't  work  at all, except for unqualified instead nothing
    rule (but what is a write-only table good for? :-). So up  to
    now they make no sense for me. Qualified instead nothing is a
    topic for a view, with the handmade negated qualification.

    Next thing will be the UPDATE NEW stuff then.

    Another rule related topic though:
    As the rule system becomes more and more useful now, wouldn't
    it   be   nice  to  enable  them  for  users?  I  think  rule
    creation/deletion must be restricted to the table  owner  (or
    superuser).  And  anything  the  rule  does has to be checked
    against the permissions of the rule owner.

    Next rule related topic:
    My new function get_ruledef(name) has now  a  little  sister.
    Her name is get_viewdef(name) and when called with a relation
    name she's telling either 'Not a view' or the complete SELECT
    statement,  that  builds  the  view. I've defined them in the
    regression database and setup a view xx_view. Now I can do

regression=> select * from xx_view;
viewname|definition

--------+--------------------------------------------------------------------------------------------------------------------------------------------------
pg_user |SELECT usename, usesysid, usecreatedb, usetrace, usesuper, usecatupd, '********'::text AS passwd, valuntil
FROMpg_shadow; 
street  |SELECT r.name, r.thepath, c.cname FROM road r, real_city c WHERE c.outline ## r.thepath;
iexit   |SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE
ih.thepath## r.thepath; 
toyemp  |SELECT name, age, location, '12'::int4 * salary AS annualsal FROM emp;
rtest_v1|SELECT a, b FROM rtest_t1;
xx_view |SELECT relname AS viewname, get_viewdef(relname) AS definition FROM pg_class WHERE (relhasrules) AND
(get_viewdef(relname)<> 'Not a view'::text); 
(6 rows)

regression=>

    (Sorry for the long lines)

    Except for the explicit type casting on all  constants,  they
    are  exactly  the  definitions of the original views. And the
    postgres parser accepts that as input to produce bit  by  bit
    the same rules/views again.

    I  tought them how to handle aggregates and group by and will
    tell them about isnull soon.

    If nobody votes against, I would like to make  them  builtins
    as  pg_get_ruledef()  and pg_get_viewdef() and then setup the
    appropriate views  (pg_rule  and  pg_view)  in  template1  by
    initdb  like we do it with pg_user. It cannot break anything,
    except that a rule action the  two  functions  cannot  handle
    will  make  the new views unusable.  But it would be a really
    powerful possibility for pg_dump or just useful to  see  what
    that damn event qual and parsetree's in pg_rewrite came from.

    Comments?


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