Rules and views (was Re: [HACKERS] Rules: 2nd patch)

> 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 FROM pg_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(at)debis(dot)com (Jan Wieck) #