Changeset: 1ff13ed045ec for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/1ff13ed045ec
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/52_describe.sql
sql/scripts/91_information_schema.sql
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/information-schema/Tests/columns.test
Branch: Dec2023
Log Message:
Improve returned data type values in views information_schema.columns,
information_schema.parameters and information_schema.routines.
For this a new function sys.sql_datatype() is created.
diffs (truncated from 478 to 300 lines):
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -6044,6 +6044,93 @@ sql_update_dec2023(Client c, mvc *sql, s
output = NULL;
}
+ /* 52_describe.sql New function sys.sql_datatype(mtype varchar(999),
digits integer, tscale integer, nameonly boolean, shortname boolean) */
+ sql_allocator *old_sa = sql->sa;
+ if ((sql->sa = sa_create(sql->pa)) != NULL) {
+ list *l;
+ if ((l = sa_list(sql->sa)) != NULL) {
+ sql_subtype t1, t2;
+ sql_find_subtype(&t1, "int", 0, 0);
+ sql_find_subtype(&t2, "boolean", 0, 0);
+ list_append(l, &tp);
+ list_append(l, &t1);
+ list_append(l, &t1);
+ list_append(l, &t2);
+ list_append(l, &t2);
+ if (!sql_bind_func_(sql, s->base.name, "sql_datatype",
l, F_FUNC, true)) {
+ const char *cmds =
+ "CREATE FUNCTION sys.sql_datatype(mtype
varchar(999), digits integer, tscale integer, nameonly boolean, shortname
boolean)\n"
+ " RETURNS varchar(1024)\n"
+ "BEGIN\n"
+ " RETURN\n"
+ " CASE mtype\n"
+ " WHEN 'char' THEN sys.ifthenelse(nameonly
OR digits <= 1, sys.ifthenelse(shortname, 'CHAR', 'CHARACTER'),
sys.ifthenelse(shortname, 'CHAR(', 'CHARACTER(') || digits || ')')\n"
+ " WHEN 'varchar' THEN
sys.ifthenelse(nameonly OR digits = 0, sys.ifthenelse(shortname, 'VARCHAR',
'CHARACTER VARYING'), sys.ifthenelse(shortname, 'VARCHAR(', 'CHARACTER
VARYING(') || digits || ')')\n"
+ " WHEN 'clob' THEN sys.ifthenelse(nameonly
OR digits = 0, sys.ifthenelse(shortname, 'CLOB', 'CHARACTER LARGE OBJECT'),
sys.ifthenelse(shortname, 'CLOB(', 'CHARACTER LARGE OBJECT(') || digits ||
')')\n"
+ " WHEN 'blob' THEN sys.ifthenelse(nameonly
OR digits = 0, sys.ifthenelse(shortname, 'BLOB', 'BINARY LARGE OBJECT'),
sys.ifthenelse(shortname, 'BLOB(', 'BINARY LARGE OBJECT(') || digits || ')')\n"
+ " WHEN 'int' THEN 'INTEGER'\n"
+ " WHEN 'bigint' THEN 'BIGINT'\n"
+ " WHEN 'smallint' THEN 'SMALLINT'\n"
+ " WHEN 'tinyint' THEN 'TINYINT'\n"
+ " WHEN 'hugeint' THEN 'HUGEINT'\n"
+ " WHEN 'boolean' THEN 'BOOLEAN'\n"
+ " WHEN 'date' THEN 'DATE'\n"
+ " WHEN 'time' THEN sys.ifthenelse(nameonly
OR digits = 1, 'TIME', 'TIME(' || (digits -1) || ')')\n"
+ " WHEN 'timestamp' THEN
sys.ifthenelse(nameonly OR digits = 7, 'TIMESTAMP', 'TIMESTAMP(' || (digits -1)
|| ')')\n"
+ " WHEN 'timestamptz' THEN
sys.ifthenelse(nameonly OR digits = 7, 'TIMESTAMP WITH TIME ZONE', 'TIMESTAMP('
|| (digits -1) || ') WITH TIME ZONE')\n"
+ " WHEN 'timetz' THEN sys.ifthenelse(nameonly
OR digits = 1, 'TIME WITH TIME ZONE', 'TIME(' || (digits -1) || ') WITH TIME
ZONE')\n"
+ " WHEN 'decimal' THEN
sys.ifthenelse(nameonly OR digits = 0, 'DECIMAL', 'DECIMAL(' || digits ||
sys.ifthenelse(tscale = 0, '', ',' || tscale) || ')')\n"
+ " WHEN 'double' THEN sys.ifthenelse(nameonly
OR (digits = 53 AND tscale = 0), sys.ifthenelse(shortname, 'DOUBLE', 'DOUBLE
PRECISION'), 'FLOAT(' || digits || ')')\n"
+ " WHEN 'real' THEN sys.ifthenelse(nameonly
OR (digits = 24 AND tscale = 0), 'REAL', 'FLOAT(' || digits || ')')\n"
+ " WHEN 'day_interval' THEN 'INTERVAL DAY'\n"
+ " WHEN 'month_interval' THEN CASE digits
WHEN 1 THEN 'INTERVAL YEAR' WHEN 2 THEN 'INTERVAL YEAR TO MONTH' WHEN 3 THEN
'INTERVAL MONTH' END\n"
+ " WHEN 'sec_interval' THEN\n"
+ " CASE digits\n"
+ " WHEN 4 THEN 'INTERVAL DAY'\n"
+ " WHEN 5 THEN 'INTERVAL DAY TO HOUR'\n"
+ " WHEN 6 THEN 'INTERVAL DAY TO MINUTE'\n"
+ " WHEN 7 THEN 'INTERVAL DAY TO SECOND'\n"
+ " WHEN 8 THEN 'INTERVAL HOUR'\n"
+ " WHEN 9 THEN 'INTERVAL HOUR TO MINUTE'\n"
+ " WHEN 10 THEN 'INTERVAL HOUR TO
SECOND'\n"
+ " WHEN 11 THEN 'INTERVAL MINUTE'\n"
+ " WHEN 12 THEN 'INTERVAL MINUTE TO
SECOND'\n"
+ " WHEN 13 THEN 'INTERVAL SECOND'\n"
+ " END\n"
+ " WHEN 'oid' THEN 'OID'\n"
+ " WHEN 'json' THEN sys.ifthenelse(nameonly
OR digits = 0, 'JSON', 'JSON(' || digits || ')')\n"
+ " WHEN 'url' THEN sys.ifthenelse(nameonly OR
digits = 0, 'URL', 'URL(' || digits || ')')\n"
+ " WHEN 'xml' THEN sys.ifthenelse(nameonly OR
digits = 0, 'XML', 'XML(' || digits || ')')\n"
+ " WHEN 'geometry' THEN\n"
+ " sys.ifthenelse(nameonly, 'GEOMETRY',\n"
+ " CASE digits\n"
+ " WHEN 4 THEN 'GEOMETRY(POINT' ||
sys.ifthenelse(tscale = 0, ')', ',' || tscale || ')')\n"
+ " WHEN 8 THEN 'GEOMETRY(LINESTRING' ||
sys.ifthenelse(tscale = 0, ')', ',' || tscale || ')')\n"
+ " WHEN 16 THEN 'GEOMETRY(POLYGON' ||
sys.ifthenelse(tscale = 0, ')', ',' || tscale || ')')\n"
+ " WHEN 20 THEN 'GEOMETRY(MULTIPOINT' ||
sys.ifthenelse(tscale = 0, ')', ',' || tscale || ')')\n"
+ " WHEN 24 THEN 'GEOMETRY(MULTILINESTRING'
|| sys.ifthenelse(tscale = 0, ')', ',' || tscale || ')')\n"
+ " WHEN 28 THEN 'GEOMETRY(MULTIPOLYGON' ||
sys.ifthenelse(tscale = 0, ')', ',' || tscale || ')')\n"
+ " WHEN 32 THEN
'GEOMETRY(GEOMETRYCOLLECTION' || sys.ifthenelse(tscale = 0, ')', ',' || tscale
|| ')')\n"
+ " ELSE 'GEOMETRY'\n"
+ " END)\n"
+ " ELSE sys.ifthenelse(mtype = lower(mtype),
upper(mtype), '\"' || mtype || '\"') || sys.ifthenelse(nameonly OR digits = 0,
'', '(' || digits || sys.ifthenelse(tscale = 0, '', ',' || tscale) || ')')\n"
+ " END;\n"
+ "END;\n"
+ "GRANT EXECUTE ON FUNCTION
sys.sql_datatype(varchar(999), integer, integer, boolean, boolean) TO PUBLIC;\n"
+ "update sys.functions set system = true where
system <> true and schema_id = 2000 and name = 'sql_datatype' and type = 1 and
language = 2;\n";
+
+ sql->session->status = 0;
+ sql->errstr[0] = '\0';
+ printf("Running database upgrade
commands:\n%s\n", cmds);
+ fflush(stdout);
+ err = SQLstatementIntern(c, cmds, "update",
true, false, NULL);
+ }
+ }
+ sa_destroy(sql->sa);
+ }
+ sql->sa = old_sa;
+
+
/* 91_information_schema.sql */
info = mvc_bind_schema(sql, "information_schema");
if (info == NULL) {
@@ -6142,14 +6229,14 @@ sql_update_dec2023(Client c, mvc *sql, s
" cast(1 + c.\"number\" AS int) AS ORDINAL_POSITION,\n"
" c.\"default\" AS COLUMN_DEFAULT,\n"
" cast(sys.ifthenelse(c.\"null\", 'YES', 'NO') AS varchar(3))
AS IS_NULLABLE,\n"
- " cast(sys.\"describe_type\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\") AS varchar(1024)) AS DATA_TYPE,\n"
+ " cast(sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\", true, true) AS varchar(1024)) AS DATA_TYPE,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('varchar','clob','char','json','url','xml') AND c.\"type_digits\" > 0,
c.\"type_digits\", NULL) AS int) AS CHARACTER_MAXIMUM_LENGTH,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('varchar','clob','char','json','url','xml') AND c.\"type_digits\" > 0,
c.\"type_digits\" * 4, NULL) AS int) AS CHARACTER_OCTET_LENGTH,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_digits\", NULL) AS int) AS NUMERIC_PRECISION,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", NULL) AS int) AS NUMERIC_SCALE,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\" -1, 0), NULL) AS int) AS
DATETIME_PRECISION,\n"
- " cast(sys.ifthenelse(c.\"type\" IN
('day_interval','month_interval','sec_interval'),
sys.\"describe_type\"(c.\"type\", c.\"type_digits\", c.\"type_scale\"), NULL)
AS varchar(40)) AS INTERVAL_TYPE,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\", c.\"type_scale\", true,
true), NULL) AS varchar(40)) AS INTERVAL_TYPE,\n"
" cast(CASE c.\"type\" WHEN 'day_interval' THEN 0 WHEN
'month_interval' THEN 0 WHEN 'sec_interval' THEN
(sys.ifthenelse(c.\"type_digits\" IN (7, 10, 12, 13),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\", 3), 0)) ELSE NULL END AS
int) AS INTERVAL_PRECISION,\n"
" cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,\n"
" cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,\n"
@@ -6271,7 +6358,7 @@ sql_update_dec2023(Client c, mvc *sql, s
" cast(NULL AS varchar(1)) AS UDT_CATALOG,\n"
" cast(NULL AS varchar(1)) AS UDT_SCHEMA,\n"
" cast(NULL AS varchar(1)) AS UDT_NAME,\n"
- " cast(CASE f.\"type\" WHEN 1 THEN
sys.\"describe_type\"(a.\"type\", a.\"type_digits\", a.\"type_scale\") WHEN 2
THEN NULL WHEN 5 THEN 'TABLE' WHEN 7 THEN 'TABLE' ELSE NULL END AS
varchar(1024)) AS DATA_TYPE,\n"
+ " cast(CASE f.\"type\" WHEN 1 THEN
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true) WHEN 2 THEN NULL WHEN 5 THEN 'TABLE' WHEN 7 THEN 'TABLE' ELSE NULL END AS
varchar(1024)) AS DATA_TYPE,\n"
" cast(sys.ifthenelse(a.\"type\" IN
('varchar','clob','char','json','url','xml') AND a.\"type_digits\" > 0,
a.\"type_digits\", NULL) AS int) AS CHARACTER_MAXIMUM_LENGTH,\n"
" cast(sys.ifthenelse(a.\"type\" IN
('varchar','clob','char','json','url','xml') AND a.\"type_digits\" > 0,
a.\"type_digits\" * 4, NULL) AS int) AS CHARACTER_OCTET_LENGTH,\n"
" cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,\n"
@@ -6284,7 +6371,7 @@ sql_update_dec2023(Client c, mvc *sql, s
" cast(sys.ifthenelse(a.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,\n"
" cast(sys.ifthenelse(a.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_scale\", NULL) AS int) AS NUMERIC_SCALE,\n"
" cast(sys.ifthenelse(a.\"type\" IN
('date','timestamp','timestamptz','time','timetz'), a.\"type_scale\" -1, NULL)
AS int) AS DATETIME_PRECISION,\n"
- " cast(sys.ifthenelse(a.\"type\" IN
('day_interval','month_interval','sec_interval'),
sys.\"describe_type\"(a.\"type\", a.\"type_digits\", a.\"type_scale\"), NULL)
AS varchar(40)) AS INTERVAL_TYPE,\n"
+ " cast(sys.ifthenelse(a.\"type\" IN
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), NULL) AS varchar(40)) AS INTERVAL_TYPE,\n"
" cast(CASE a.\"type\" WHEN 'day_interval' THEN 0 WHEN
'month_interval' THEN 0 WHEN 'sec_interval' THEN
(sys.ifthenelse(a.\"type_digits\" IN (7, 10, 12, 13),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\", 3), 0)) ELSE NULL END AS
int) AS INTERVAL_PRECISION,\n"
" cast(NULL AS varchar(1)) AS TYPE_UDT_CATALOG,\n"
" cast(NULL AS varchar(1)) AS TYPE_UDT_SCHEMA,\n"
@@ -6377,7 +6464,7 @@ sql_update_dec2023(Client c, mvc *sql, s
" cast(NULL AS varchar(1)) AS TO_SQL_SPECIFIC_CATALOG,\n"
" cast(NULL AS varchar(1)) AS TO_SQL_SPECIFIC_SCHEMA,\n"
" cast(NULL AS varchar(1)) AS TO_SQL_SPECIFIC_NAME,\n"
- " cast(sys.\"describe_type\"(a.\"type\", a.\"type_digits\",
a.\"type_scale\") AS varchar(1024)) AS DATA_TYPE,\n"
+ " cast(sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\",
a.\"type_scale\", true, true) AS varchar(1024)) AS DATA_TYPE,\n"
" cast(sys.ifthenelse(a.\"type\" IN
('varchar','clob','char','json','url','xml') AND a.\"type_digits\" > 0,
a.\"type_digits\", NULL) AS int) AS CHARACTER_MAXIMUM_LENGTH,\n"
" cast(sys.ifthenelse(a.\"type\" IN
('varchar','clob','char','json','url','xml') AND a.\"type_digits\" > 0,
a.\"type_digits\" * 4, NULL) AS int) AS CHARACTER_OCTET_LENGTH,\n"
" cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,\n"
@@ -6390,7 +6477,7 @@ sql_update_dec2023(Client c, mvc *sql, s
" cast(sys.ifthenelse(a.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,\n"
" cast(sys.ifthenelse(a.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_scale\", NULL) AS int) AS NUMERIC_SCALE,\n"
" cast(sys.ifthenelse(a.\"type\" IN
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\" -1, 0), NULL) AS int) AS
DATETIME_PRECISION,\n"
- " cast(sys.ifthenelse(a.\"type\" IN
('day_interval','month_interval','sec_interval'),
sys.\"describe_type\"(a.\"type\", a.\"type_digits\", a.\"type_scale\"), NULL)
AS varchar(40)) AS INTERVAL_TYPE,\n"
+ " cast(sys.ifthenelse(a.\"type\" IN
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), NULL) AS varchar(40)) AS INTERVAL_TYPE,\n"
" cast(CASE a.\"type\" WHEN 'day_interval' THEN 0 WHEN
'month_interval' THEN 0 WHEN 'sec_interval' THEN
(sys.ifthenelse(a.\"type_digits\" IN (7, 10, 12, 13),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\", 3), 0)) ELSE NULL END AS
int) AS INTERVAL_PRECISION,\n"
" cast(NULL AS varchar(1)) AS UDT_CATALOG,\n"
" cast(NULL AS varchar(1)) AS UDT_SCHEMA,\n"
@@ -6469,7 +6556,7 @@ sql_update_dec2023(Client c, mvc *sql, s
"GRANT EXECUTE ON FUNCTION sys.persist_unlogged(string)
TO PUBLIC;\n"
"GRANT EXECUTE ON FUNCTION sys.persist_unlogged(string,
string) TO PUBLIC;\n"
"UPDATE sys.functions SET system = true WHERE system <>
true AND\n"
- "name = 'persist_unlogged' AND schema_id = 2000;\n";
+ "name = 'persist_unlogged' AND schema_id = 2000 AND
type = 5 AND language = 1;\n";
printf("Running database upgrade commands:\n%s\n", query);
fflush(stdout);
err = SQLstatementIntern(c, query, "update", true, false, NULL);
diff --git a/sql/scripts/52_describe.sql b/sql/scripts/52_describe.sql
--- a/sql/scripts/52_describe.sql
+++ b/sql/scripts/52_describe.sql
@@ -6,6 +6,67 @@
--
-- Copyright 1997 - July 2008 CWI, August 2008 - 2023 MonetDB B.V.
+CREATE FUNCTION sys.sql_datatype(mtype varchar(999), digits integer, tscale
integer, nameonly boolean, shortname boolean)
+ RETURNS varchar(1024)
+BEGIN
+ RETURN
+ CASE mtype
+ WHEN 'char' THEN sys.ifthenelse(nameonly OR digits <= 1,
sys.ifthenelse(shortname, 'CHAR', 'CHARACTER'), sys.ifthenelse(shortname,
'CHAR(', 'CHARACTER(') || digits || ')')
+ WHEN 'varchar' THEN sys.ifthenelse(nameonly OR digits = 0,
sys.ifthenelse(shortname, 'VARCHAR', 'CHARACTER VARYING'),
sys.ifthenelse(shortname, 'VARCHAR(', 'CHARACTER VARYING(') || digits || ')')
+ WHEN 'clob' THEN sys.ifthenelse(nameonly OR digits = 0,
sys.ifthenelse(shortname, 'CLOB', 'CHARACTER LARGE OBJECT'),
sys.ifthenelse(shortname, 'CLOB(', 'CHARACTER LARGE OBJECT(') || digits || ')')
+ WHEN 'blob' THEN sys.ifthenelse(nameonly OR digits = 0,
sys.ifthenelse(shortname, 'BLOB', 'BINARY LARGE OBJECT'),
sys.ifthenelse(shortname, 'BLOB(', 'BINARY LARGE OBJECT(') || digits || ')')
+ WHEN 'int' THEN 'INTEGER'
+ WHEN 'bigint' THEN 'BIGINT'
+ WHEN 'smallint' THEN 'SMALLINT'
+ WHEN 'tinyint' THEN 'TINYINT'
+ WHEN 'hugeint' THEN 'HUGEINT'
+ WHEN 'boolean' THEN 'BOOLEAN'
+ WHEN 'date' THEN 'DATE'
+ WHEN 'time' THEN sys.ifthenelse(nameonly OR digits = 1, 'TIME', 'TIME(' ||
(digits -1) || ')')
+ WHEN 'timestamp' THEN sys.ifthenelse(nameonly OR digits = 7, 'TIMESTAMP',
'TIMESTAMP(' || (digits -1) || ')')
+ WHEN 'timestamptz' THEN sys.ifthenelse(nameonly OR digits = 7, 'TIMESTAMP
WITH TIME ZONE', 'TIMESTAMP(' || (digits -1) || ') WITH TIME ZONE')
+ WHEN 'timetz' THEN sys.ifthenelse(nameonly OR digits = 1, 'TIME WITH TIME
ZONE', 'TIME(' || (digits -1) || ') WITH TIME ZONE')
+ WHEN 'decimal' THEN sys.ifthenelse(nameonly OR digits = 0, 'DECIMAL',
'DECIMAL(' || digits || sys.ifthenelse(tscale = 0, '', ',' || tscale) || ')')
+ WHEN 'double' THEN sys.ifthenelse(nameonly OR (digits = 53 AND tscale =
0), sys.ifthenelse(shortname, 'DOUBLE', 'DOUBLE PRECISION'), 'FLOAT(' || digits
|| ')')
+ WHEN 'real' THEN sys.ifthenelse(nameonly OR (digits = 24 AND tscale = 0),
'REAL', 'FLOAT(' || digits || ')')
+ WHEN 'day_interval' THEN 'INTERVAL DAY'
+ WHEN 'month_interval' THEN CASE digits WHEN 1 THEN 'INTERVAL YEAR' WHEN 2
THEN 'INTERVAL YEAR TO MONTH' WHEN 3 THEN 'INTERVAL MONTH' END
+ WHEN 'sec_interval' THEN
+ CASE digits
+ WHEN 4 THEN 'INTERVAL DAY'
+ WHEN 5 THEN 'INTERVAL DAY TO HOUR'
+ WHEN 6 THEN 'INTERVAL DAY TO MINUTE'
+ WHEN 7 THEN 'INTERVAL DAY TO SECOND'
+ WHEN 8 THEN 'INTERVAL HOUR'
+ WHEN 9 THEN 'INTERVAL HOUR TO MINUTE'
+ WHEN 10 THEN 'INTERVAL HOUR TO SECOND'
+ WHEN 11 THEN 'INTERVAL MINUTE'
+ WHEN 12 THEN 'INTERVAL MINUTE TO SECOND'
+ WHEN 13 THEN 'INTERVAL SECOND'
+ END
+ WHEN 'oid' THEN 'OID'
+ WHEN 'json' THEN sys.ifthenelse(nameonly OR digits = 0, 'JSON', 'JSON(' ||
digits || ')')
+ WHEN 'url' THEN sys.ifthenelse(nameonly OR digits = 0, 'URL', 'URL(' ||
digits || ')')
+ WHEN 'xml' THEN sys.ifthenelse(nameonly OR digits = 0, 'XML', 'XML(' ||
digits || ')')
+ WHEN 'geometry' THEN
+ sys.ifthenelse(nameonly, 'GEOMETRY',
+ CASE digits
+ WHEN 4 THEN 'GEOMETRY(POINT' || sys.ifthenelse(tscale = 0, ')', ',' ||
tscale || ')')
+ WHEN 8 THEN 'GEOMETRY(LINESTRING' || sys.ifthenelse(tscale = 0, ')',
',' || tscale || ')')
+ WHEN 16 THEN 'GEOMETRY(POLYGON' || sys.ifthenelse(tscale = 0, ')', ','
|| tscale || ')')
+ WHEN 20 THEN 'GEOMETRY(MULTIPOINT' || sys.ifthenelse(tscale = 0, ')',
',' || tscale || ')')
+ WHEN 24 THEN 'GEOMETRY(MULTILINESTRING' || sys.ifthenelse(tscale = 0,
')', ',' || tscale || ')')
+ WHEN 28 THEN 'GEOMETRY(MULTIPOLYGON' || sys.ifthenelse(tscale = 0, ')',
',' || tscale || ')')
+ WHEN 32 THEN 'GEOMETRY(GEOMETRYCOLLECTION' || sys.ifthenelse(tscale =
0, ')', ',' || tscale || ')')
+ ELSE 'GEOMETRY'
+ END)
+ ELSE sys.ifthenelse(mtype = lower(mtype), upper(mtype), '"' || mtype ||
'"') || sys.ifthenelse(nameonly OR digits = 0, '', '(' || digits ||
sys.ifthenelse(tscale = 0, '', ',' || tscale) || ')')
+ END;
+END;
+
+GRANT EXECUTE ON FUNCTION sys.sql_datatype(varchar(999), integer, integer,
boolean, boolean) TO PUBLIC;
+
+-- ToDo improve sys.describe_type() by calling sql_datatype(ctype, digits,
tscale, false, false)
CREATE FUNCTION sys.describe_type(ctype string, digits integer, tscale integer)
RETURNS string
BEGIN
diff --git a/sql/scripts/91_information_schema.sql
b/sql/scripts/91_information_schema.sql
--- a/sql/scripts/91_information_schema.sql
+++ b/sql/scripts/91_information_schema.sql
@@ -129,14 +129,14 @@ CREATE VIEW INFORMATION_SCHEMA.COLUMNS A
cast(1 + c."number" AS int) AS ORDINAL_POSITION,
c."default" AS COLUMN_DEFAULT,
cast(sys.ifthenelse(c."null", 'YES', 'NO') AS varchar(3)) AS IS_NULLABLE,
- cast(sys."describe_type"(c."type", c."type_digits", c."type_scale") AS
varchar(1024)) AS DATA_TYPE,
+ cast(sys."sql_datatype"(c."type", c."type_digits", c."type_scale", true,
true) AS varchar(1024)) AS DATA_TYPE,
cast(sys.ifthenelse(c."type" IN ('varchar','clob','char','json','url','xml')
AND c."type_digits" > 0, c."type_digits", NULL) AS int) AS
CHARACTER_MAXIMUM_LENGTH,
cast(sys.ifthenelse(c."type" IN ('varchar','clob','char','json','url','xml')
AND c."type_digits" > 0, c."type_digits" * 4, NULL) AS int) AS
CHARACTER_OCTET_LENGTH,
cast(sys.ifthenelse(c."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c."type_digits", NULL) AS int) AS NUMERIC_PRECISION,
cast(sys.ifthenelse(c."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c."type" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,
cast(sys.ifthenelse(c."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c."type_scale", NULL) AS int) AS NUMERIC_SCALE,
cast(sys.ifthenelse(c."type" IN
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(c."type_scale" > 0, c."type_scale" -1, 0), NULL) AS int) AS
DATETIME_PRECISION,
- cast(sys.ifthenelse(c."type" IN
('day_interval','month_interval','sec_interval'), sys."describe_type"(c."type",
c."type_digits", c."type_scale"), NULL) AS varchar(40)) AS INTERVAL_TYPE,
+ cast(sys.ifthenelse(c."type" IN
('day_interval','month_interval','sec_interval'), sys."sql_datatype"(c."type",
c."type_digits", c."type_scale", true, true), NULL) AS varchar(40)) AS
INTERVAL_TYPE,
cast(CASE c."type" WHEN 'day_interval' THEN 0 WHEN 'month_interval' THEN 0
WHEN 'sec_interval' THEN (sys.ifthenelse(c."type_digits" IN (7, 10, 12, 13),
sys.ifthenelse(c."type_scale" > 0, c."type_scale", 3), 0)) ELSE NULL END AS
int) AS INTERVAL_PRECISION,
cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,
cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,
@@ -275,7 +275,7 @@ CREATE VIEW INFORMATION_SCHEMA.ROUTINES
cast(NULL AS varchar(1)) AS UDT_CATALOG,
cast(NULL AS varchar(1)) AS UDT_SCHEMA,
cast(NULL AS varchar(1)) AS UDT_NAME,
- cast(CASE f."type" WHEN 1 THEN sys."describe_type"(a."type",
a."type_digits", a."type_scale") WHEN 2 THEN NULL WHEN 5 THEN 'TABLE' WHEN 7
THEN 'TABLE' ELSE NULL END AS varchar(1024)) AS DATA_TYPE,
+ cast(CASE f."type" WHEN 1 THEN sys."sql_datatype"(a."type", a."type_digits",
a."type_scale", true, true) WHEN 2 THEN NULL WHEN 5 THEN 'TABLE' WHEN 7 THEN
'TABLE' ELSE NULL END AS varchar(1024)) AS DATA_TYPE,
cast(sys.ifthenelse(a."type" IN ('varchar','clob','char','json','url','xml')
AND a."type_digits" > 0, a."type_digits", NULL) AS int) AS
CHARACTER_MAXIMUM_LENGTH,
cast(sys.ifthenelse(a."type" IN ('varchar','clob','char','json','url','xml')
AND a."type_digits" > 0, a."type_digits" * 4, NULL) AS int) AS
CHARACTER_OCTET_LENGTH,
cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,
@@ -288,7 +288,7 @@ CREATE VIEW INFORMATION_SCHEMA.ROUTINES
cast(sys.ifthenelse(a."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a."type" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,
cast(sys.ifthenelse(a."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a."type_scale", NULL) AS int) AS NUMERIC_SCALE,
cast(sys.ifthenelse(a."type" IN
('date','timestamp','timestamptz','time','timetz'), a."type_scale" -1, NULL) AS
int) AS DATETIME_PRECISION,
- cast(sys.ifthenelse(a."type" IN
('day_interval','month_interval','sec_interval'), sys."describe_type"(a."type",
a."type_digits", a."type_scale"), NULL) AS varchar(40)) AS INTERVAL_TYPE,
+ cast(sys.ifthenelse(a."type" IN
('day_interval','month_interval','sec_interval'), sys."sql_datatype"(a."type",
a."type_digits", a."type_scale", true, true), NULL) AS varchar(40)) AS
INTERVAL_TYPE,
cast(CASE a."type" WHEN 'day_interval' THEN 0 WHEN 'month_interval' THEN 0
WHEN 'sec_interval' THEN (sys.ifthenelse(a."type_digits" IN (7, 10, 12, 13),
sys.ifthenelse(a."type_scale" > 0, a."type_scale", 3), 0)) ELSE NULL END AS
int) AS INTERVAL_PRECISION,
cast(NULL AS varchar(1)) AS TYPE_UDT_CATALOG,
cast(NULL AS varchar(1)) AS TYPE_UDT_SCHEMA,
@@ -387,7 +387,7 @@ CREATE VIEW INFORMATION_SCHEMA.PARAMETER
cast(NULL AS varchar(1)) AS TO_SQL_SPECIFIC_CATALOG,
cast(NULL AS varchar(1)) AS TO_SQL_SPECIFIC_SCHEMA,
cast(NULL AS varchar(1)) AS TO_SQL_SPECIFIC_NAME,
- cast(sys."describe_type"(a."type", a."type_digits", a."type_scale") AS
varchar(1024)) AS DATA_TYPE,
+ cast(sys."sql_datatype"(a."type", a."type_digits", a."type_scale", true,
true) AS varchar(1024)) AS DATA_TYPE,
cast(sys.ifthenelse(a."type" IN ('varchar','clob','char','json','url','xml')
AND a."type_digits" > 0, a."type_digits", NULL) AS int) AS
CHARACTER_MAXIMUM_LENGTH,
cast(sys.ifthenelse(a."type" IN ('varchar','clob','char','json','url','xml')
AND a."type_digits" > 0, a."type_digits" * 4, NULL) AS int) AS
CHARACTER_OCTET_LENGTH,
cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,
@@ -400,7 +400,7 @@ CREATE VIEW INFORMATION_SCHEMA.PARAMETER
cast(sys.ifthenelse(a."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a."type" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,
cast(sys.ifthenelse(a."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a."type_scale", NULL) AS int) AS NUMERIC_SCALE,
cast(sys.ifthenelse(a."type" IN
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(a."type_scale" > 0, a."type_scale" -1, 0), NULL) AS int) AS
DATETIME_PRECISION,
- cast(sys.ifthenelse(a."type" IN
('day_interval','month_interval','sec_interval'), sys."describe_type"(a."type",
a."type_digits", a."type_scale"), NULL) AS varchar(40)) AS INTERVAL_TYPE,
+ cast(sys.ifthenelse(a."type" IN
('day_interval','month_interval','sec_interval'), sys."sql_datatype"(a."type",
a."type_digits", a."type_scale", true, true), NULL) AS varchar(40)) AS
INTERVAL_TYPE,
cast(CASE a."type" WHEN 'day_interval' THEN 0 WHEN 'month_interval' THEN 0
WHEN 'sec_interval' THEN (sys.ifthenelse(a."type_digits" IN (7, 10, 12, 13),
sys.ifthenelse(a."type_scale" > 0, a."type_scale", 3), 0)) ELSE NULL END AS
int) AS INTERVAL_PRECISION,
cast(NULL AS varchar(1)) AS UDT_CATALOG,
cast(NULL AS varchar(1)) AS UDT_SCHEMA,
diff --git a/sql/test/emptydb/Tests/check.stable.out
b/sql/test/emptydb/Tests/check.stable.out
--- a/sql/test/emptydb/Tests/check.stable.out
+++ b/sql/test/emptydb/Tests/check.stable.out
@@ -444,13 +444,13 @@ select 'null in fkeys.delete_action', de
% .%25, .s, .t, ., ., .t, ., ., . #
table_name
% %25, name, name, query, type, system, commit_action, access, comment
# name
% char, varchar, varchar, clob, varchar,
boolean, char, char, varchar # type
-% 11, 18, 34, 6771, 5, 5, 8, 10, 0 # length
+% 11, 18, 34, 6793, 5, 5, 8, 10, 0 # length
[ "sys._tables", "information_schema", "character_sets", "create
view information_schema.character_sets as select cast(null as varchar(1)) as
character_set_catalog, cast(null as varchar(1)) as character_set_schema,
cast('UTF-8' as varchar(16)) as character_set_name, cast('ISO/IEC 10646:2021'
as varchar(20)) as character_repertoire, cast('UTF-8' as varchar(16)) as
form_of_use, cast(null as varchar(1)) as default_collate_catalog, cast(null as
varchar(1)) as default_collate_schema, cast(null as varchar(1)) as
default_collate_name;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
[ "sys._tables", "information_schema", "check_constraints", "create
view information_schema.check_constraints as select cast(null as varchar(1)) as
constraint_catalog, cast(null as varchar(1024)) as constraint_schema, cast(null
as varchar(1024)) as constraint_name, cast(null as varchar(1024)) as
check_clause where 1=0;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
-[ "sys._tables", "information_schema", "columns", "create view
information_schema.columns as select cast(null as varchar(1)) as table_catalog,
s.\"name\" as table_schema, t.\"name\" as table_name, c.\"name\" as
column_name, cast(1 + c.\"number\" as int) as ordinal_position, c.\"default\"
as column_default, cast(sys.ifthenelse(c.\"null\", 'YES', 'NO') as varchar(3))
as is_nullable, cast(sys.\"describe_type\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\") as varchar(1024)) as data_type,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml')
and c.\"type_digits\" > 0, c.\"type_digits\", null) as int) as
character_maximum_length, cast(sys.ifthenelse(c.\"type\" in
('varchar','clob','char','json','url','xml') and c.\"type_digits\" > 0,
c.\"type_digits\" * 4, null) as int) as character_octet_length,
cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_digits\", null) as int) a
s numeric_precision, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(c.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(c.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"describe_type\"(c.\"type\", c.\"type_digits\", c.\"type_scale\"), null)
as varchar(40)) as interval_type, cast(case c.\"type\" when 'day_interval' then
0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(c.\"type_digits\" in (7, 10, 12, 13),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"
type_scale\", 3), 0)) else null end as int) as interval_precision, cast(null
as varchar(1)) as character_set_catalog, cast(null as varchar(1)) as
character_set_schema, cast(sys.ifthenelse(c.\"type\" in
('varchar','clob','char','json','url','xml'), 'UTF-8', null) as varchar(16)) as
character_set_name, cast(null as varchar(1)) as collation_catalog, cast(null as
varchar(1)) as collation_schema, cast(null as varchar(1)) as collation_name,
cast(null as varchar(1)) as domain_catalog, cast(null as varchar(1)) as
domain_schema, cast(null as varchar(1)) as domain_name, cast(null as
varchar(1)) as udt_catalog, cast(null as varchar(1)) as udt_schema, cast(null
as varchar(1)) as udt_name, cast(null as varchar(1)) as scope_catalog,
cast(null as varchar(1)) as scope_schema, cast(null as varchar(1)) as
scope_name, cast(null as int) as maximum_cardinality, cast(null as varchar(1))
as dtd_identifier, cast('NO' as varchar(3)) as is_self_referencing,
cast(sys.ifthenelse(seq.\"name\" is null or c.\"nul
l\", 'NO', 'YES') as varchar(3)) as is_identity, seq.\"name\" as
identity_generation, seq.\"start\" as identity_start, seq.\"increment\" as
identity_increment, seq.\"maxvalue\" as identity_maximum, seq.\"minvalue\" as
identity_minimum, cast(sys.ifthenelse(seq.\"name\" is null, null,
sys.ifthenelse(seq.\"cycle\", 'YES', 'NO')) as varchar(3)) as identity_cycle,
cast(sys.ifthenelse(seq.\"name\" is null, 'NO', 'YES') as varchar(3)) as
is_generated, cast(sys.ifthenelse(seq.\"name\" is null, null, c.\"default\") as
varchar(1024)) as generation_expression, cast('NO' as varchar(3)) as
is_system_time_period_start, cast('NO' as varchar(3)) as
is_system_time_period_end, cast('NO' as varchar(3)) as
system_time_period_timestamp_generation, cast(sys.ifthenelse(t.\"type\" in
(0,3,7,20,30), 'YES', 'NO') as varchar(3)) as is_updatable, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as declared_numeric_scale,
t.\"schema_id\" as schem
a_id, c.\"table_id\" as table_id, c.\"id\" as column_id, seq.\"id\" as
sequence_id, t.\"system\" as is_system, cm.\"remark\" as comments from
sys.\"columns\" c inner join sys.\"tables\" t on c.\"table_id\" = t.\"id\"
inner join sys.\"schemas\" s on t.\"schema_id\" = s.\"id\" left outer join
sys.\"comments\" cm on c.\"id\" = cm.\"id\" left outer join sys.\"sequences\"
seq on ((seq.\"name\"||'\"') = substring(c.\"default\", 3 +
sys.\"locate\"('\".\"seq_',c.\"default\",14))) order by s.\"name\", t.\"name\",
c.\"number\";", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
-[ "sys._tables", "information_schema", "parameters", "create view
information_schema.parameters as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(sys.ifthenelse((a.\"inout\" = 0 or f.\"type\" = 2), 1 + a.\"number\",
sys.ifthenelse(f.\"type\" = 1, a.\"number\", (1 + a.\"number\" -
f.count_out_cols))) as int) as ordinal_position,
cast(sys.ifthenelse(a.\"inout\" = 0, 'OUT', sys.ifthenelse(a.\"inout\" = 1,
'IN', 'INOUT')) as varchar(5)) as parameter_mode,
cast(sys.ifthenelse(a.\"inout\" = 0, 'YES', 'NO') as varchar(3)) as is_result,
cast(null as varchar(1)) as as_locator, a.\"name\" as parameter_name, cast(null
as varchar(1)) as from_sql_specific_catalog, cast(null as varchar(1)) as
from_sql_specific_schema, cast(null as varchar(1)) as from_sql_specific_name,
cast(null as varchar(1)) as to_sql_specific_catalog, cast(null as varchar(1))
as to_sql_specific_schema, cast(null as
varchar(1)) as to_sql_specific_name, cast(sys.\"describe_type\"(a.\"type\",
a.\"type_digits\", a.\"type_scale\") as varchar(1024)) as data_type,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml')
and a.\"type_digits\" > 0, a.\"type_digits\", null) as int) as
character_maximum_length, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\" * 4, null) as int) as character_octet_length, cast(null as
varchar(1)) as character_set_catalog, cast(null as varchar(1)) as
character_set_schema, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml'), 'UTF-8', null) as varchar(16)) as
character_set_name, cast(null as varchar(1)) as collation_catalog, cast(null as
varchar(1)) as collation_schema, cast(null as varchar(1)) as collation_name,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_
digits\", null) as int) as numeric_precision, cast(sys.ifthenelse(a.\"type\"
in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"describe_type\"(a.\"type\", a.\"type_digits\", a.\"type_scale\"), null)
as varchar(40)) as interval_type, cast(case a.\"type\" when 'day_interval' then
0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"type_digits\" in (7, 10, 12, 13), sys.ifthenelse(a
.\"type_scale\" > 0, a.\"type_scale\", 3), 0)) else null end as int) as
interval_precision, cast(null as varchar(1)) as udt_catalog, cast(null as
varchar(1)) as udt_schema, cast(null as varchar(1)) as udt_name, cast(null as
varchar(1)) as scope_catalog, cast(null as varchar(1)) as scope_schema,
cast(null as varchar(1)) as scope_name, cast(null as int) as
maximum_cardinality, cast(null as varchar(1)) as dtd_identifier, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as declared_numeric_scale,
cast(null as varchar(1)) as parameter_default, f.\"schema_id\" as schema_id,
f.\"id\" as function_id, a.\"id\" as arg_id, f.\"name\" as function_name,
f.\"type\" as function_type, f.\"system\" as is_system from sys.\"args\" a
inner join (select fun.*, (select count(*) from sys.args a0 where a0.inout = 0
and a0.func_id = fun.id) as count_out_cols from sys.\"functions\" fun where
fun.\"type\" in (1, 2, 5, 7)) f on f.\"id\" = a.\"fu
nc_id\" inner join sys.\"schemas\" s on s.\"id\" = f.\"schema_id\" order by
s.\"name\", f.\"name\", f.\"id\", a.\"inout\" desc, a.\"number\";",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "information_schema", "columns", "create view
information_schema.columns as select cast(null as varchar(1)) as table_catalog,
s.\"name\" as table_schema, t.\"name\" as table_name, c.\"name\" as
column_name, cast(1 + c.\"number\" as int) as ordinal_position, c.\"default\"
as column_default, cast(sys.ifthenelse(c.\"null\", 'YES', 'NO') as varchar(3))
as is_nullable, cast(sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\", true, true) as varchar(1024)) as data_type,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml')
and c.\"type_digits\" > 0, c.\"type_digits\", null) as int) as
character_maximum_length, cast(sys.ifthenelse(c.\"type\" in
('varchar','clob','char','json','url','xml') and c.\"type_digits\" > 0,
c.\"type_digits\" * 4, null) as int) as character_octet_length,
cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_digits\", null
) as int) as numeric_precision, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(c.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(c.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\", c.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case c.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(c.\"type_digits\" in (7, 10, 12, 13), sys.ifthenelse(c.\"
type_scale\" > 0, c.\"type_scale\", 3), 0)) else null end as int) as
interval_precision, cast(null as varchar(1)) as character_set_catalog,
cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(null as varchar(1)) as domain_catalog,
cast(null as varchar(1)) as domain_schema, cast(null as varchar(1)) as
domain_name, cast(null as varchar(1)) as udt_catalog, cast(null as varchar(1))
as udt_schema, cast(null as varchar(1)) as udt_name, cast(null as varchar(1))
as scope_catalog, cast(null as varchar(1)) as scope_schema, cast(null as
varchar(1)) as scope_name, cast(null as int) as maximum_cardinality, cast(null
as varchar(1)) as dtd_identifier, cast('NO' as varchar(3)) as
is_self_referencing, cast(sys.ifthenelse(seq.\"nam
e\" is null or c.\"null\", 'NO', 'YES') as varchar(3)) as is_identity,
seq.\"name\" as identity_generation, seq.\"start\" as identity_start,
seq.\"increment\" as identity_increment, seq.\"maxvalue\" as identity_maximum,
seq.\"minvalue\" as identity_minimum, cast(sys.ifthenelse(seq.\"name\" is null,
null, sys.ifthenelse(seq.\"cycle\", 'YES', 'NO')) as varchar(3)) as
identity_cycle, cast(sys.ifthenelse(seq.\"name\" is null, 'NO', 'YES') as
varchar(3)) as is_generated, cast(sys.ifthenelse(seq.\"name\" is null, null,
c.\"default\") as varchar(1024)) as generation_expression, cast('NO' as
varchar(3)) as is_system_time_period_start, cast('NO' as varchar(3)) as
is_system_time_period_end, cast('NO' as varchar(3)) as
system_time_period_timestamp_generation, cast(sys.ifthenelse(t.\"type\" in
(0,3,7,20,30), 'YES', 'NO') as varchar(3)) as is_updatable, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as declared_numeric_scale, t.
\"schema_id\" as schema_id, c.\"table_id\" as table_id, c.\"id\" as column_id,
seq.\"id\" as sequence_id, t.\"system\" as is_system, cm.\"remark\" as comments
from sys.\"columns\" c inner join sys.\"tables\" t on c.\"table_id\" = t.\"id\"
inner join sys.\"schemas\" s on t.\"schema_id\" = s.\"id\" left outer join
sys.\"comments\" cm on c.\"id\" = cm.\"id\" left outer join sys.\"sequences\"
seq on ((seq.\"name\"||'\"') = substring(c.\"default\", 3 +
sys.\"locate\"('\".\"seq_',c.\"default\",14))) order by s.\"name\", t.\"name\",
c.\"number\";", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
