Re: SQL99 CREATE TABLE ... (LIKE parent_table)

Rod Taylor kirjutas T, 13.05.2003 kell 15:40:
> On Tue, 2003-05-13 at 05:08, Hannu Krosing wrote:
> > Tom Lane kirjutas E, 12.05.2003 kell 17:59:
> > > Rod Taylor <rbt(at)rbt(dot)ca> writes:
> > > > Quick patch to add the subject. Restructures all inheritance to consist
> > > > of a few flags to indicate which structures we want to inherit
> > >
> > > I think overloading the inheritance mechanism to serve this purpose is a
> > > bad idea.
> >
> > Are you sure that LIKE is not _supposed_ to be inheritable. I mean that
> > changes done to the LIKE table should carry over to the table defined
> > using LIKE:
>
> > create table template( i int);
> > create table instance(like template, t text);
> > alter table template add column j int;
> >
> > and now instance should have columns i,j,t .
>
> It is described as being replaced by the column definitions within the
> new table, with no mention of connection to the parent table..
>
> 11.3, 6, d) "The <like clause> is effectively replaced by NCi, 1 (one)
> <= i <= cnt"

Must "11.3, 6, d)" hold only at table create time or should the
relationship stay the same afterwards as well ?

The reason I ask is that I was hoping that we can replace our current
(multiple) INHERITS mechanism with combination of:

* (single) UNDER which could inherit all constraints, including
PRIMARY KEY and UNIQUE, by storing the UNDER tables in the same
physical relation,

and

* (multiple) LIKE tables which would be able to inherit row-level level
constraints (NOT NULL, CHECK, FOREIGN KEY)

so that no functionality we already have would be lost but the logical
problems of inheriting constraints (what to do with multiple PK's and
UNIQUES) could be solved.

I would have also liked to twist the specs a little so that

create table t1 (a text, i int);
create table t2 (b text, i int);
create table t3 (like t1, like t2);

would yield t3 as (a text, i int, b text) and in be simultaneously of
types t1, t2 and t3 so that a function defined over t1 or t2 could be
used directly on t3 as well.

also i would propose that functions be resolved dynamically

so that

create table t1 (a text, i int)
create table t2 (b text) under t1;

select func(t1) from t1;

would use func(t1) on rows from t1 and func(t2) on rows from t2;

This would give PostgreSQL a nice minimal OO type system.

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