[RFC] SQL improvement: allow computed fields to be defined as a domain [CORE4476]

[firebird-automations]

Submitted by: Robert (rj1102)

Normal fields and identity fields (as of FB3) can be defined both as data type and domain. However computed fields can only be defined as datatype.

To have a more consistent and better support of the SQL standard, I like to request that computed fields can also be defined as a domain. Above that, using domains is a good way to standarize data type usage in your database, so it is good idea to have them also available for computed fields.

For example:

SQL-2003 standard for field definition (shortened): <column definition> ::= <column name> [ <data type> | <domain name> ]

CREATE TABLE MyTable ( MyComputedField GENERATED ALWAYS AS (2*2), /* data type will be determined implicitly */ MyComputedField INTEGER GENERATED ALWAYS AS (2*2), /* computed field as explicit data type */ MyComputedField NumericDomain GENEARTED ALWAYS AS (2*2) /* computed field as explicit domain, -- not supported yet -- */ )

[katalym]

Hi FireBird! Could you please review this feature request and implement it? At least for 4.0+.

This is a simple sample which is expected to work:

CREATE DOMAIN DOM_VARCHAR_10 AS VARCHAR(10);
-- create domain based computed field
ALTER TABLE TEST_TABLE ADD FLD_COMPUTED DOM_VARCHAR_10 COMPUTED BY (cast(2+2 as DOM_VARCHAR_10));
-- alter domain based computed field
ALTER TABLE TEST_TABLE ALTER FLD_COMPUTED TYPE DOM_VARCHAR_10 COMPUTED BY (cast(2+2 as DOM_VARCHAR_10));

And this SQL: select rdb$field_source from rdb$relation_fields where rdb$relation_name='TEST_TABLE' and rdb$field_name='FLD_COMPUTED' should return DOM_VARCHAR_10

Also please consider:

1. if the cast(... as DOM_VARCHAR_10) is present in COMPYTED BY then this should be enough to explicitly set the field's type to domain DOM_VARCHAR_10.
2. If domain is specified as the field's type then cast(... as DOM_VARCHAR_10) should not be required in the COMPYTED BY declaration - cast should be done automatically.

Thank you, Pavel Katalymov. BroadView Software Inc