Re: SQL 2003 conformance

On Tue, 19 Oct 2004 23:06:07 +0200, Peter Eisentraut wrote:

>> I basically needed
>> data to compare SQL:2003 with PostgreSQL's current
>> INFORMATION_SCHEMA.SQL_FEATURES view; hence, I created
>> http://troels.arvin.dk/db/rdbms/sql-standard/2003/sql:2003-features.sql
>
> Yes, about 45% of the job that I proposed would be updating
> sql_features.txt with the new feature set

That has been done, see latest part of
http://troels.arvin.dk/db/pgsql/conformance/pgsql-sql-conformance.patch

What I've done is add the new SQL:2003-stuff and initially set all the new
stuff to 'NO' in conformance status. Elein and I are now selectively
changing NO to YES where appropriate. Some NOs have been changed to YES
already (B122, E081, F053, F672, T071); on the TODO-list is to look closer
at

B034 | Dynamic specification of cursor attributes
S095 | Array constructors by query
S096 | Optional array bounds
S097 | Array element assignment
S201-01 | SQL-invoked routines on arrays - Array parameters
S201-02 | SQL-invoked routines on arrays - Array as result type of functions
S291 | Unique constraint on entire row
T053 | Explicit aliases for all-fields reference
T061 | UCS support
T152 | DISTINCT predicate with negation
T172 | AS subquery clause in table definition
T272 | Enhanced savepoint management
T326 | Table functions
T631 | IN predicate with one list element
T641 | Multiple column assignment
T651 | SQL-schema statements in SQL routines
T652 | SQL-dynamic statements in SQL routines
T653 | SQL-schema statements in external routines
T654 | SQL-dynamic statements in external routines
T655 | Cyclically dependent routines

I didn't include the feature IDs from SQL/CLI, SQL/JRT, SQL/MED, SQL/OLB,
SQL/PSM and SQL/XML in the patch; do you think that those feature IDs
should be included? - I doubt that we will have time to look at all that
stuff, but if someone thinks that PostgreSQL 8 might actually implement
features from some of those feature sets, please tell us which of them to
look closer at. If someone _knows_ that the mentioned feature sets are
_not_ implemented, then I'll simply include them in the patch and set
conformance to NO.

An obvious question is how strict to be:

I believe that PostgreSQL 8 doesn't implement SQL:2003's IDENTITY columns,
although a primary key column of type SERIAL (and a proper trigger in case
of the ALLWAYS identity variant) can be used for the same thing.

Sequences are allmost implemented, except PostgreSQL doesn't have the NEXT
VALUE FOR (uses nextval()). As NEXT VALUE FOR functionality is rather
important for sequences, one might say that it's important for the syntax
to be equal here.

--
Greetings from Troels Arvin, Copenhagen, Denmark