Changeset: 7432d4b9ec97 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/7432d4b9ec97
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: default
Log Message:
Fix and approve information_schema upgrade code.
diffs (truncated from 3699 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
@@ -6301,9 +6301,9 @@ sql_update_default(Client c, mvc *sql, s
" cast(NULL AS varchar(1)) AS SCOPE_NAME,\n"
" cast(NULL AS int) AS MAXIMUM_CARDINALITY,\n"
" cast(NULL AS int) AS DTD_IDENTIFIER,\n"
- " cast(sys.\"ifthenelse\"(sys.\"locate\"('begin',f.\"func\") >
0, sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", sys.\"locate\"('begin',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\")))-1), sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\"))), NULL) AS varchar(8196) AS ROUTINE_BODY,\n"
+ " cast(sys.\"ifthenelse\"(sys.\"locate\"('begin',f.\"func\") >
0, sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", sys.\"locate\"('begin',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\")))-1), sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\"))), NULL) AS varchar(8196)) AS
ROUTINE_BODY,\n"
" f.\"func\" AS ROUTINE_DEFINITION,\n"
- " cast(sys.\"ifthenelse\"(sys.\"locate\"('external
name',f.\"func\") > 0, sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external name',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external
name',f.\"func\")))-1), sys.\"substring\"(f.\"func\", 14 +
sys.\"locate\"('external name',f.\"func\"))), NULL) AS varchar(1024) AS
EXTERNAL_NAME,\n"
+ " cast(sys.\"ifthenelse\"(sys.\"locate\"('external
name',f.\"func\") > 0, sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external name',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external
name',f.\"func\")))-1), sys.\"substring\"(f.\"func\", 14 +
sys.\"locate\"('external name',f.\"func\"))), NULL) AS varchar(1024)) AS
EXTERNAL_NAME,\n"
" fl.\"language_keyword\" AS EXTERNAL_LANGUAGE,\n"
" 'GENERAL' AS PARAMETER_STYLE,\n"
" 'YES' AS IS_DETERMINISTIC,\n"
@@ -6374,7 +6374,7 @@ sql_update_default(Client c, mvc *sql, s
" cast(NULL AS varchar(1)) AS SEQUENCE_CATALOG,\n"
" s.\"name\" AS SEQUENCE_SCHEMA,\n"
" sq.\"name\" AS SEQUENCE_NAME,\n"
- " cast('bigint' AS varchar(16)) AS DATA_TYPE,\n"
+ " cast('BIGINT' AS varchar(16)) AS DATA_TYPE,\n"
" cast(64 AS SMALLINT) AS NUMERIC_PRECISION,\n"
" cast(2 AS SMALLINT) AS NUMERIC_PRECISION_RADIX,\n"
" cast(0 AS SMALLINT) AS NUMERIC_SCALE,\n"
diff --git
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
---
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -596,12 +596,12 @@ CREATE VIEW INFORMATION_SCHEMA.COLUMNS A
s."name" AS TABLE_SCHEMA,
t."name" AS TABLE_NAME,
c."name" AS COLUMN_NAME,
- cast(c."number" +1 AS int) AS ORDINAL_POSITION,
+ 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,
- CASE c."type" WHEN 'day_interval' THEN 'interval day' WHEN 'month_interval'
THEN 'interval month' WHEN 'sec_interval' THEN 'interval second' ELSE c."type"
END AS DATA_TYPE,
- cast(sys.ifthenelse(c."type" IN
('varchar','clob','char','json','url','xml'), c."type_digits", NULL) AS int) AS
CHARACTER_MAXIMUM_LENGTH,
- cast(sys.ifthenelse(c."type" IN
('varchar','clob','char','json','url','xml'), c."type_digits" * 3, NULL) AS
int) AS CHARACTER_OCTET_LENGTH,
+ 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) 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,
@@ -710,11 +710,116 @@ CREATE VIEW INFORMATION_SCHEMA.REFERENTI
LEFT OUTER JOIN sys."schemas" uks ON uks."id" = ukt."schema_id"
ORDER BY s."name", t."name", fk."name";
GRANT SELECT ON TABLE INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS TO PUBLIC
WITH GRANT OPTION;
+CREATE VIEW INFORMATION_SCHEMA.ROUTINES AS SELECT
+ cast(NULL AS varchar(1)) AS SPECIFIC_CATALOG,
+ s."name" AS SPECIFIC_SCHEMA,
+ cast(f."id" as varchar(10)) AS SPECIFIC_NAME,
+ cast(NULL AS varchar(1)) AS ROUTINE_CATALOG,
+ s."name" AS ROUTINE_SCHEMA,
+ f."name" AS ROUTINE_NAME,
+ ft."function_type_keyword" AS ROUTINE_TYPE,
+ cast(NULL AS varchar(1)) AS MODULE_CATALOG,
+ cast(NULL AS varchar(1)) AS MODULE_SCHEMA,
+ cast(f."mod" AS varchar(128)) AS MODULE_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(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(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,
+ 'UTF-8' 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'), 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(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,
+ cast(NULL AS varchar(1)) AS TYPE_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 int) AS DTD_IDENTIFIER,
+ cast(sys."ifthenelse"(sys."locate"('begin',f."func") > 0,
sys."ifthenelse"(sys."endswith"(f."func",';'), sys."substring"(f."func",
sys."locate"('begin',f."func"), sys."length"(sys."substring"(f."func",
sys."locate"('begin',f."func")))-1), sys."substring"(f."func",
sys."locate"('begin',f."func"))), NULL) AS varchar(8196)) AS ROUTINE_BODY,
+ f."func" AS ROUTINE_DEFINITION,
+ cast(sys."ifthenelse"(sys."locate"('external name',f."func") > 0,
sys."ifthenelse"(sys."endswith"(f."func",';'), sys."substring"(f."func", 14 +
sys."locate"('external name',f."func"), sys."length"(sys."substring"(f."func",
14 + sys."locate"('external name',f."func")))-1), sys."substring"(f."func", 14
+ sys."locate"('external name',f."func"))), NULL) AS varchar(1024)) AS
EXTERNAL_NAME,
+ fl."language_keyword" AS EXTERNAL_LANGUAGE,
+ 'GENERAL' AS PARAMETER_STYLE,
+ 'YES' AS IS_DETERMINISTIC,
+ cast(sys.ifthenelse(f."side_effect", 'MODIFIES', 'READ') AS varchar(10)) AS
SQL_DATA_ACCESS,
+ cast(CASE f."type" WHEN 2 THEN NULL ELSE 'NO' END AS varchar(3)) AS
IS_NULL_CALL,
+ cast(NULL AS varchar(1)) AS SQL_PATH,
+ cast(NULL AS varchar(1)) AS SCHEMA_LEVEL_ROUTINE,
+ cast(NULL AS int) AS MAX_DYNAMIC_RESULT_SETS,
+ cast(NULL AS varchar(1)) AS IS_USER_DEFINED_CAST,
+ cast(NULL AS varchar(1)) AS IS_IMPLICITLY_INVOCABLE,
+ cast(NULL AS varchar(1)) AS SECURITY_TYPE,
+ 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(NULL AS varchar(1)) AS AS_LOCATOR,
+ cast(NULL AS timestamp) AS CREATED,
+ cast(NULL AS timestamp) AS LAST_ALTERED,
+ cast(NULL AS varchar(1)) AS NEW_SAVEPOINT_LEVEL,
+ cast(NULL AS varchar(1)) AS IS_UDT_DEPENDENT,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_FROM_DATA_TYPE,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_AS_LOCATOR,
+ cast(NULL AS int) AS RESULT_CAST_CHAR_MAX_LENGTH,
+ cast(NULL AS int) AS RESULT_CAST_CHAR_OCTET_LENGTH,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_CHAR_SET_CATALOG,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_CHAR_SET_SCHEMA,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_CHARACTER_SET_NAME,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_COLLATION_CATALOG,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_COLLATION_SCHEMA,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_COLLATION_NAME,
+ cast(NULL AS int) AS RESULT_CAST_NUMERIC_PRECISION,
+ cast(NULL AS int) AS RESULT_CAST_NUMERIC_RADIX,
+ cast(NULL AS int) AS RESULT_CAST_NUMERIC_SCALE,
+ cast(NULL AS int) AS RESULT_CAST_DATETIME_PRECISION,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_INTERVAL_TYPE,
+ cast(NULL AS int) AS RESULT_CAST_INTERVAL_PRECISION,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_TYPE_UDT_CATALOG,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_TYPE_UDT_SCHEMA,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_TYPE_UDT_NAME,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_SCOPE_CATALOG,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_SCOPE_SCHEMA,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_SCOPE_NAME,
+ cast(NULL AS int) AS RESULT_CAST_MAX_CARDINALITY,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_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 RESULT_CAST_FROM_DECLARED_DATA_TYPE,
+ cast(NULL AS int) AS RESULT_CAST_DECLARED_NUMERIC_PRECISION,
+ cast(NULL AS int) AS RESULT_CAST_DECLARED_NUMERIC_SCALE,
+ -- MonetDB column extensions
+ f."schema_id" AS schema_id,
+ f."id" AS function_id,
+ f."type" AS function_type,
+ f."language" AS function_language,
+ f."system" AS is_system,
+ cm."remark" AS comments
+ FROM sys."functions" f
+ INNER JOIN sys."schemas" s ON s."id" = f."schema_id"
+ INNER JOIN sys."function_types" ft ON ft."function_type_id" = f."type"
+ INNER JOIN sys."function_languages" fl ON fl."language_id" = f."language"
+ LEFT OUTER JOIN sys."args" a ON a."func_id" = f."id" and a."inout" = 0 and
a."number" = 0
+ LEFT OUTER JOIN sys."comments" cm ON cm."id" = f."id"
+ WHERE f."type" in (1, 2, 5, 7)
+ ORDER BY s."name", f."name";
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.ROUTINES TO PUBLIC WITH GRANT OPTION;
CREATE VIEW INFORMATION_SCHEMA.SEQUENCES AS SELECT
cast(NULL AS varchar(1)) AS SEQUENCE_CATALOG,
s."name" AS SEQUENCE_SCHEMA,
sq."name" AS SEQUENCE_NAME,
- cast('bigint' AS varchar(16)) AS DATA_TYPE,
+ cast('BIGINT' AS varchar(16)) AS DATA_TYPE,
cast(64 AS SMALLINT) AS NUMERIC_PRECISION,
cast(2 AS SMALLINT) AS NUMERIC_PRECISION_RADIX,
cast(0 AS SMALLINT) AS NUMERIC_SCALE,
@@ -740,7 +845,7 @@ GRANT SELECT ON TABLE INFORMATION_SCHEMA
update sys._tables set system = true where system <> true
and schema_id = (select s.id from sys.schemas s where s.name =
'information_schema')
- and name in
('character_sets','check_constraints','columns','schemata','sequences','referential_constraints','table_constraints','tables','views');
+ and name in
('character_sets','check_constraints','columns','routines','schemata','sequences','referential_constraints','table_constraints','tables','views');
Running database upgrade commands:
CREATE FUNCTION sys.persist_unlogged()
diff --git a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
@@ -596,12 +596,12 @@ CREATE VIEW INFORMATION_SCHEMA.COLUMNS A
s."name" AS TABLE_SCHEMA,
t."name" AS TABLE_NAME,
c."name" AS COLUMN_NAME,
- cast(c."number" +1 AS int) AS ORDINAL_POSITION,
+ 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,
- CASE c."type" WHEN 'day_interval' THEN 'interval day' WHEN 'month_interval'
THEN 'interval month' WHEN 'sec_interval' THEN 'interval second' ELSE c."type"
END AS DATA_TYPE,
- cast(sys.ifthenelse(c."type" IN
('varchar','clob','char','json','url','xml'), c."type_digits", NULL) AS int) AS
CHARACTER_MAXIMUM_LENGTH,
- cast(sys.ifthenelse(c."type" IN
('varchar','clob','char','json','url','xml'), c."type_digits" * 3, NULL) AS
int) AS CHARACTER_OCTET_LENGTH,
+ 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) 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,
@@ -710,11 +710,116 @@ CREATE VIEW INFORMATION_SCHEMA.REFERENTI
LEFT OUTER JOIN sys."schemas" uks ON uks."id" = ukt."schema_id"
ORDER BY s."name", t."name", fk."name";
GRANT SELECT ON TABLE INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS TO PUBLIC
WITH GRANT OPTION;
+CREATE VIEW INFORMATION_SCHEMA.ROUTINES AS SELECT
+ cast(NULL AS varchar(1)) AS SPECIFIC_CATALOG,
+ s."name" AS SPECIFIC_SCHEMA,
+ cast(f."id" as varchar(10)) AS SPECIFIC_NAME,
+ cast(NULL AS varchar(1)) AS ROUTINE_CATALOG,
+ s."name" AS ROUTINE_SCHEMA,
+ f."name" AS ROUTINE_NAME,
+ ft."function_type_keyword" AS ROUTINE_TYPE,
+ cast(NULL AS varchar(1)) AS MODULE_CATALOG,
+ cast(NULL AS varchar(1)) AS MODULE_SCHEMA,
+ cast(f."mod" AS varchar(128)) AS MODULE_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(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(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,
+ 'UTF-8' 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'), 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(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,
+ cast(NULL AS varchar(1)) AS TYPE_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 int) AS DTD_IDENTIFIER,
+ cast(sys."ifthenelse"(sys."locate"('begin',f."func") > 0,
sys."ifthenelse"(sys."endswith"(f."func",';'), sys."substring"(f."func",
sys."locate"('begin',f."func"), sys."length"(sys."substring"(f."func",
sys."locate"('begin',f."func")))-1), sys."substring"(f."func",
sys."locate"('begin',f."func"))), NULL) AS varchar(8196)) AS ROUTINE_BODY,
+ f."func" AS ROUTINE_DEFINITION,
+ cast(sys."ifthenelse"(sys."locate"('external name',f."func") > 0,
sys."ifthenelse"(sys."endswith"(f."func",';'), sys."substring"(f."func", 14 +
sys."locate"('external name',f."func"), sys."length"(sys."substring"(f."func",
14 + sys."locate"('external name',f."func")))-1), sys."substring"(f."func", 14
+ sys."locate"('external name',f."func"))), NULL) AS varchar(1024)) AS
EXTERNAL_NAME,
+ fl."language_keyword" AS EXTERNAL_LANGUAGE,
+ 'GENERAL' AS PARAMETER_STYLE,
+ 'YES' AS IS_DETERMINISTIC,
+ cast(sys.ifthenelse(f."side_effect", 'MODIFIES', 'READ') AS varchar(10)) AS
SQL_DATA_ACCESS,
+ cast(CASE f."type" WHEN 2 THEN NULL ELSE 'NO' END AS varchar(3)) AS
IS_NULL_CALL,
+ cast(NULL AS varchar(1)) AS SQL_PATH,
+ cast(NULL AS varchar(1)) AS SCHEMA_LEVEL_ROUTINE,
+ cast(NULL AS int) AS MAX_DYNAMIC_RESULT_SETS,
+ cast(NULL AS varchar(1)) AS IS_USER_DEFINED_CAST,
+ cast(NULL AS varchar(1)) AS IS_IMPLICITLY_INVOCABLE,
+ cast(NULL AS varchar(1)) AS SECURITY_TYPE,
+ 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(NULL AS varchar(1)) AS AS_LOCATOR,
+ cast(NULL AS timestamp) AS CREATED,
+ cast(NULL AS timestamp) AS LAST_ALTERED,
+ cast(NULL AS varchar(1)) AS NEW_SAVEPOINT_LEVEL,
+ cast(NULL AS varchar(1)) AS IS_UDT_DEPENDENT,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_FROM_DATA_TYPE,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_AS_LOCATOR,
+ cast(NULL AS int) AS RESULT_CAST_CHAR_MAX_LENGTH,
+ cast(NULL AS int) AS RESULT_CAST_CHAR_OCTET_LENGTH,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_CHAR_SET_CATALOG,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_CHAR_SET_SCHEMA,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_CHARACTER_SET_NAME,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_COLLATION_CATALOG,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_COLLATION_SCHEMA,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_COLLATION_NAME,
+ cast(NULL AS int) AS RESULT_CAST_NUMERIC_PRECISION,
+ cast(NULL AS int) AS RESULT_CAST_NUMERIC_RADIX,
+ cast(NULL AS int) AS RESULT_CAST_NUMERIC_SCALE,
+ cast(NULL AS int) AS RESULT_CAST_DATETIME_PRECISION,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_INTERVAL_TYPE,
+ cast(NULL AS int) AS RESULT_CAST_INTERVAL_PRECISION,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_TYPE_UDT_CATALOG,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_TYPE_UDT_SCHEMA,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_TYPE_UDT_NAME,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_SCOPE_CATALOG,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_SCOPE_SCHEMA,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_SCOPE_NAME,
+ cast(NULL AS int) AS RESULT_CAST_MAX_CARDINALITY,
+ cast(NULL AS varchar(1)) AS RESULT_CAST_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 RESULT_CAST_FROM_DECLARED_DATA_TYPE,
+ cast(NULL AS int) AS RESULT_CAST_DECLARED_NUMERIC_PRECISION,
+ cast(NULL AS int) AS RESULT_CAST_DECLARED_NUMERIC_SCALE,
+ -- MonetDB column extensions
+ f."schema_id" AS schema_id,
+ f."id" AS function_id,
+ f."type" AS function_type,
+ f."language" AS function_language,
+ f."system" AS is_system,
+ cm."remark" AS comments
+ FROM sys."functions" f
+ INNER JOIN sys."schemas" s ON s."id" = f."schema_id"
+ INNER JOIN sys."function_types" ft ON ft."function_type_id" = f."type"
+ INNER JOIN sys."function_languages" fl ON fl."language_id" = f."language"
+ LEFT OUTER JOIN sys."args" a ON a."func_id" = f."id" and a."inout" = 0 and
a."number" = 0
+ LEFT OUTER JOIN sys."comments" cm ON cm."id" = f."id"
+ WHERE f."type" in (1, 2, 5, 7)
+ ORDER BY s."name", f."name";
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.ROUTINES TO PUBLIC WITH GRANT OPTION;
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
