Re: OOP real life example (was Re: Why is MySQL more

On Mon, 2002-08-12 at 11:38, Mario Weilguni wrote:
> Am Montag, 12. August 2002 08:02 schrieb Don Baccus:
> > Curt Sampson wrote:
> > > On Sun, 11 Aug 2002, Don Baccus wrote:
> > >>I've been wanting to point out that SQL views are really, when
> > >>scrutinized, "just syntactic sugar" ...
> > >
> > > Oh? Ok, please translate the following into equivalant SQL that
> > > does not use a view:
> > >
> > > CREATE TABLE t1 (key serial, value1 text, value2 text);
> > > CREATE VIEW v1 AS SELECT key, value1 FROM t1;
> > > GRANT SELECT ON v1 TO sorin;
> >
> > Granulize GRANT to the table column level. Then GRANT "SELECT" perms
> > for the user on every column from the two tables that happen to be
> > included in the view.
> >
> > Yes, it's awkward. So are the VIEW-based replacements for PG's type
> > extensibility features.
>
> But this is not a replacement for a view, isn't it? With a view I can do this:
> create view v1 as select name, salary from workers where type <> 'MANAGEMENT';
>
> with column permissions I must give access to all workers salary including the management, but not with a view.

I guess that bare-bones replacement of CREATE VIEW with CREATE TABLE and
CREATE RULE ... ON SELECT DO INSTEAD ... would have exaclty the same
semantics as CREATE VIEW, including the ability to GRANT .

so the no-view-syntactic-sugar equivalent would be

CREATE TABLE v1 AS SELECT * FROM t1 WHERE false;
CREATE RULE v1ins AS
ON SELECT TO tv1
DO INSTEAD
SELECT t1."key",
t1.value2
FROM t1
WHERE (t1."type" <> 'MANAGEMENT'::text);
GRANT SELECT ON v1 TO sorin;

Actually it seems that GRANT is also syntactic sugar for rules and the
above could be replaced with

CREATE RULE v1ins AS
ON SELECT TO tv1
DO INSTEAD
SELECT t1."key",
t1.value2
FROM t1
WHERE (t1."type" <> 'MANAGEMENT'::text)
AND CURRENT_USER IN ( SELECT username
FROM grantees
WHERE tablename = 'v1'
AND command = 'select' )
INSERT INTO GRANTEES(tablename,command,username)
VALUES('v1','select','sorin');

----------------
Hannu