I've just updated features.sgml to have a list of supported features
followed by a list of unsupported ones. There are some items in the
"unsupported list" which look easy to do. I've got patches for a "MATCH
SIMPLE" clause on referential integrity declarations, and am developing
patches for CREATE CAST, DROP CAST, and probably a few more items which
could be simple parser additions or changes. Look at

  http://developer.postgresql.org/docs/postgres/features.html

for the most recent version (it is not there yet but I'd expect it to be
there soon).

I'd like to look at supporting the various shorthand literal notations
like B'0101', X'ABCD', and N'national' in a better way in the lexer to
make sure that the information gets to the parser more robustly. Right
now, N'national' is not supported at all and the other two forms are
supported by transforming the input locally in the lexer which seems to
work but does not extend to the NATIONAL CHARACTER case. Comments and
suggestions are welcome.

Y'all may want to look at features.sgml to find projects if you are
looking for something to do; there are several items which look to be
relatively easy to accomplish and others at various levels of
difficulty...

                    - Thomas

Tue, 18 Jun 2002 23:23:17 -0700
Thomas Lockhart <lockhart@fourpalms.org>
SQL99 feature list
On Wed, 2002-06-19 at 08:23, Thomas Lockhart wrote:
> I've just updated features.sgml to have a list of supported features
> followed by a list of unsupported ones.

It seems you have to move (T171, LIKE clause in table definition) to
unsupported :

hannu=# \dt    
    List of relations
  Name   | Type  | Owner 
---------+-------+-------
 t1      | table | hannu
(1 row)

hannu=# create table t11 (a int,like t1);
ERROR:  parser: parse error at or near "like"

hannu=# create table t11 (a int) inherits (t1);
CREATE

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

19 Jun 2002 16:01:41 +0200
Hannu Krosing <hannu@tm.ee>
Re: SQL99 feature list
> It seems you have to move (T171, LIKE clause in table definition) to
> unsupported :
> hannu=# create table t11 (a int,like t1);
> ERROR:  parser: parse error at or near "like"

Ah, that's what that means! I'll move it to "unsupported", but it seems
like it might be fairly easy to implement, eh? Anyone interested in
looking at it?

                     - Thomas

Wed, 19 Jun 2002 07:05:47 -0700
Thomas Lockhart <lockhart@fourpalms.org>
Re: SQL99 feature list
> It seems like a little more powerful version of PGs INHERITS

What makes it "more powerful"? I'd guess that it is an attribute copy
rather than a declaration of inheritance and could be based on our
existing "create table as" feature.

...
> I can see some features that are not listed in neither of your feature
> lists, like
> * ON COMMIT <table commit action> ROWS
> * <subtable clause> ::= UNDER <supertable clause>

Hmm. I worked from the SQL99 draft document we have found on the web.
Hopefully the list is not completely orthogonal to the released
standard; it took several hours to transform the list and to look
through it for a first cut :(

                    - Thomas

Wed, 19 Jun 2002 07:42:43 -0700
Thomas Lockhart <lockhart@fourpalms.org>
Re: SQL99 feature list
On Wed, 2002-06-19 at 16:05, Thomas Lockhart wrote:
> > It seems you have to move (T171, LIKE clause in table definition) to
> > unsupported :
> > hannu=# create table t11 (a int,like t1);
> > ERROR:  parser: parse error at or near "like"
> 
> Ah, that's what that means! I'll move it to "unsupported", but it seems
> like it might be fairly easy to implement, eh?

It seems like a little more powerful version of PGs INHERITS 

> Anyone interested in looking at it?

This is the full <table definition> BNF from ISO 9075

I can see some features that are not listed in neither of your feature
lists, like

* ON COMMIT <table commit action> ROWS
* <subtable clause> ::= UNDER <supertable clause> 

-----------------------------------

11.3 <table definition> 

<table definition> ::=
    CREATE [ <table scope> ] TABLE <table name>
    <table contents source> 
    [ ON COMMIT <table commit action> ROWS ]

<table contents source> ::=
    <table element list>
  | OF <user-defined type>
       [ <subtable clause> ]
       [ <table element list> ]

<table scope> ::= <global or local> TEMPORARY

<global or local> ::= 
    GLOBAL
  | LOCAL

<table commit action> ::=
    PRESERVE
  | DELETE

<table element list> ::= 
    <left paren>
    <table element> [ { <comma> <table element> }... ] 
    <right paren>

<table element> ::=
    <column definition>
  | <table constraint definition>
  | <like clause>
  | <self-referencing column specification>
  | <column options> 

<self-referencing column specification> ::= 
    REF IS <self-referencing column name> <reference generation> 

<reference generation> ::=
    SYSTEM GENERATED
  | USER GENERATED 
  | DERIVED 

<self-referencing column name> ::= <column name>

<column options> ::= <column name> WITH OPTIONS <column option list> 

<column option list> ::=
    [ <scope clause> ] 
    [ <default clause> ] 
    [ <column constraint definition>... ] 
    [ <collate clause> ]

<subtable clause> ::= UNDER <supertable clause> 

<supertable clause> ::= <supertable name> 


<supertable name> ::= <table name> 

<like clause> ::= LIKE <table name> 

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


19 Jun 2002 17:31:08 +0200
Hannu Krosing <hannu@tm.ee>
Re: SQL99 feature list
On Wed, 2002-06-19 at 16:42, Thomas Lockhart wrote:
> > It seems like a little more powerful version of PGs INHERITS
> 
> What makes it "more powerful"?


> I'd guess that it is an attribute copy
> rather than a declaration of inheritance and could be based on our
> existing "create table as" feature.

I said "a little" more powerful :)

You can specify where the "inherited" table fields are placed which you
cant when using INHERITS.

I think that 95% of current use of INHERITS is in cases where you don't
actually want inheritance :)

Regarding which you can probably move (S111: ONLY in query expressions)
to supported, with a notice that we dont support (single-inheritance)
UNDER but have our own notion of multiple inheritance.

I have ideas how to implement UNDER (put everything in one table so that
primary keys et. al. are more easyly inherited) but it will be viable
only after the same obstacles that fast DROP COLUMN are overcome.

> > I can see some features that are not listed in neither of your feature
> > lists, like
> > * ON COMMIT <table commit action> ROWS
> > * <subtable clause> ::= UNDER <supertable clause>
> 
> Hmm. I worked from the SQL99 draft document we have found on the web.

I used the file ansi-iso-9075-2-1999.pdf from

http://www.cse.iitb.ac.in:8000/proxy/db/~dbms/Data/Papers-Other/SQL1999/

which has a fat red FINAL stamp on the front page, but I'm not sure how
legal it is to download it :)

> Hopefully the list is not completely orthogonal to the released
> standard; it took several hours to transform the list and to look
> through it for a first cut :(

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


19 Jun 2002 17:58:58 +0200
Hannu Krosing <hannu@tm.ee>
Re: SQL99 feature list