Changeset: b768717b7023 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/b768717b7023
Branch: balanced_union
Log Message:
merged with default
diffs (truncated from 976 to 300 lines):
diff --git a/sql/ChangeLog b/sql/ChangeLog
--- a/sql/ChangeLog
+++ b/sql/ChangeLog
@@ -11,6 +11,7 @@
information_schema.check_constraints
information_schema.table_constraints
information_schema.referential_constraints
+ information_schema.routines
information_schema.sequences
Most views have been extended (after the standard columns) with MonetDB
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
@@ -5938,7 +5938,7 @@ sql_update_default(Client c, mvc *sql, s
" JOIN sys.schemas ON tables.schema_id = schemas.id\n"
" JOIN sys.privilege_codes pc ON p.privileges =
pc.privilege_code_id\n"
" WHERE roles.name = current_role;\n"
- "GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;\n"
+ "GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;\n"
"update sys._tables set system = true where system <> true and
schema_id = 2000 and name = 'describe_accessible_tables';\n"
/* PYTHON_MAP and PYTHON3_MAP have been removed */
@@ -6142,12 +6142,12 @@ sql_update_default(Client c, mvc *sql, s
" s.\"name\" AS TABLE_SCHEMA,\n"
" t.\"name\" AS TABLE_NAME,\n"
" c.\"name\" AS COLUMN_NAME,\n"
- " cast(c.\"number\" +1 AS int) AS ORDINAL_POSITION,\n"
+ " 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"
- " 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,\n"
- " cast(sys.ifthenelse(c.\"type\" IN
('varchar','clob','char','json','url','xml'), c.\"type_digits\", NULL) AS int)
AS CHARACTER_MAXIMUM_LENGTH,\n"
- " cast(sys.ifthenelse(c.\"type\" IN
('varchar','clob','char','json','url','xml'), c.\"type_digits\" * 3, NULL) AS
int) AS CHARACTER_OCTET_LENGTH,\n"
+ " cast(sys.\"describe_type\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\") 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"
@@ -6264,6 +6264,112 @@ sql_update_default(Client c, mvc *sql, s
" ORDER BY s.\"name\", t.\"name\", fk.\"name\";\n"
"GRANT SELECT ON TABLE
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS TO PUBLIC WITH GRANT OPTION;\n"
+ "CREATE VIEW INFORMATION_SCHEMA.ROUTINES AS SELECT\n"
+ " cast(NULL AS varchar(1)) AS SPECIFIC_CATALOG,\n"
+ " s.\"name\" AS SPECIFIC_SCHEMA,\n"
+ " cast(f.\"id\" as varchar(10)) AS SPECIFIC_NAME,\n"
+ " cast(NULL AS varchar(1)) AS ROUTINE_CATALOG,\n"
+ " s.\"name\" AS ROUTINE_SCHEMA,\n"
+ " f.\"name\" AS ROUTINE_NAME,\n"
+ " ft.\"function_type_keyword\" AS ROUTINE_TYPE,\n"
+ " cast(NULL AS varchar(1)) AS MODULE_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS MODULE_SCHEMA,\n"
+ " cast(f.\"mod\" AS varchar(128)) AS MODULE_NAME,\n"
+ " 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(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"
+ " cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,\n"
+ " 'UTF-8' AS CHARACTER_SET_NAME,\n"
+ " cast(NULL AS varchar(1)) AS COLLATION_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS COLLATION_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS COLLATION_NAME,\n"
+ " 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,\n"
+ " 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(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"
+ " cast(NULL AS varchar(1)) AS TYPE_UDT_NAME,\n"
+ " cast(NULL AS varchar(1)) AS SCOPE_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS SCOPE_SCHEMA,\n"
+ " 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"
+ " 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"
+ " fl.\"language_keyword\" AS EXTERNAL_LANGUAGE,\n"
+ " 'GENERAL' AS PARAMETER_STYLE,\n"
+ " 'YES' AS IS_DETERMINISTIC,\n"
+ " cast(sys.ifthenelse(f.\"side_effect\", 'MODIFIES', 'READ')
AS varchar(10)) AS SQL_DATA_ACCESS,\n"
+ " cast(CASE f.\"type\" WHEN 2 THEN NULL ELSE 'NO' END AS
varchar(3)) AS IS_NULL_CALL,\n"
+ " cast(NULL AS varchar(1)) AS SQL_PATH,\n"
+ " cast(NULL AS varchar(1)) AS SCHEMA_LEVEL_ROUTINE,\n"
+ " cast(NULL AS int) AS MAX_DYNAMIC_RESULT_SETS,\n"
+ " cast(NULL AS varchar(1)) AS IS_USER_DEFINED_CAST,\n"
+ " cast(NULL AS varchar(1)) AS IS_IMPLICITLY_INVOCABLE,\n"
+ " cast(NULL AS varchar(1)) AS SECURITY_TYPE,\n"
+ " 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(NULL AS varchar(1)) AS AS_LOCATOR,\n"
+ " cast(NULL AS timestamp) AS CREATED,\n"
+ " cast(NULL AS timestamp) AS LAST_ALTERED,\n"
+ " cast(NULL AS varchar(1)) AS NEW_SAVEPOINT_LEVEL,\n"
+ " cast(NULL AS varchar(1)) AS IS_UDT_DEPENDENT,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_FROM_DATA_TYPE,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_AS_LOCATOR,\n"
+ " cast(NULL AS int) AS RESULT_CAST_CHAR_MAX_LENGTH,\n"
+ " cast(NULL AS int) AS RESULT_CAST_CHAR_OCTET_LENGTH,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_CHAR_SET_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_CHAR_SET_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS
RESULT_CAST_CHARACTER_SET_NAME,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_COLLATION_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_COLLATION_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_COLLATION_NAME,\n"
+ " cast(NULL AS int) AS RESULT_CAST_NUMERIC_PRECISION,\n"
+ " cast(NULL AS int) AS RESULT_CAST_NUMERIC_RADIX,\n"
+ " cast(NULL AS int) AS RESULT_CAST_NUMERIC_SCALE,\n"
+ " cast(NULL AS int) AS RESULT_CAST_DATETIME_PRECISION,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_INTERVAL_TYPE,\n"
+ " cast(NULL AS int) AS RESULT_CAST_INTERVAL_PRECISION,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_TYPE_UDT_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_TYPE_UDT_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_TYPE_UDT_NAME,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_SCOPE_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_SCOPE_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_SCOPE_NAME,\n"
+ " cast(NULL AS int) AS RESULT_CAST_MAX_CARDINALITY,\n"
+ " cast(NULL AS varchar(1)) AS RESULT_CAST_DTD_IDENTIFIER,\n"
+ " cast(NULL AS varchar(1)) AS DECLARED_DATA_TYPE,\n"
+ " cast(NULL AS int) AS DECLARED_NUMERIC_PRECISION,\n"
+ " cast(NULL AS int) AS DECLARED_NUMERIC_SCALE,\n"
+ " cast(NULL AS varchar(1)) AS
RESULT_CAST_FROM_DECLARED_DATA_TYPE,\n"
+ " cast(NULL AS int) AS
RESULT_CAST_DECLARED_NUMERIC_PRECISION,\n"
+ " cast(NULL AS int) AS RESULT_CAST_DECLARED_NUMERIC_SCALE,\n"
+ " -- MonetDB column extensions\n"
+ " f.\"schema_id\" AS schema_id,\n"
+ " f.\"id\" AS function_id,\n"
+ " f.\"type\" AS function_type,\n"
+ " f.\"language\" AS function_language,\n"
+ " f.\"system\" AS is_system,\n"
+ " cm.\"remark\" AS comments\n"
+ " FROM sys.\"functions\" f\n"
+ " INNER JOIN sys.\"schemas\" s ON s.\"id\" = f.\"schema_id\"\n"
+ " INNER JOIN sys.\"function_types\" ft ON
ft.\"function_type_id\" = f.\"type\"\n"
+ " INNER JOIN sys.\"function_languages\" fl ON
fl.\"language_id\" = f.\"language\"\n"
+ " LEFT OUTER JOIN sys.\"args\" a ON a.\"func_id\" = f.\"id\"
and a.\"inout\" = 0 and a.\"number\" = 0\n"
+ " LEFT OUTER JOIN sys.\"comments\" cm ON cm.\"id\" = f.\"id\"\n"
+ " WHERE f.\"type\" in (1, 2, 5, 7)\n"
+ " ORDER BY s.\"name\", f.\"name\";\n"
+ "GRANT SELECT ON TABLE INFORMATION_SCHEMA.ROUTINES TO PUBLIC
WITH GRANT OPTION;\n"
+
"CREATE VIEW INFORMATION_SCHEMA.SEQUENCES AS SELECT\n"
" cast(NULL AS varchar(1)) AS SEQUENCE_CATALOG,\n"
" s.\"name\" AS SEQUENCE_SCHEMA,\n"
@@ -6294,7 +6400,7 @@ sql_update_default(Client c, mvc *sql, s
"\n"
"update sys._tables set system = true where system <> true\n"
" and schema_id = (select s.id from sys.schemas s where s.name
= 'information_schema')\n"
- " and name in
('character_sets','check_constraints','columns','schemata','sequences','referential_constraints','table_constraints','tables','views');\n";
+ " and name in
('character_sets','check_constraints','columns','routines','schemata','sequences','referential_constraints','table_constraints','tables','views');\n";
printf("Running database upgrade commands:\n%s\n", cmds);
fflush(stdout);
err = SQLstatementIntern(c, cmds, "update", true, false, NULL);
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
@@ -126,12 +126,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,
@@ -256,6 +256,116 @@ CREATE VIEW INFORMATION_SCHEMA.REFERENTI
GRANT SELECT ON TABLE INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS TO PUBLIC
WITH GRANT OPTION;
+-- The view ROUTINES contains all functions and procedures in the current
database.
+-- Only those functions and procedures are shown that the current user has
access to
+-- (by way of being the owner or having some privilege).
+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, -- TODO: replace with full
routine signature string. Note sys.fully_qualified_functions.nme does not
produce the correct signature.
+ 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) -- 1=Scalar function, 2=Procedure, 3=Function
returning a table, 7=Loader function
+ ORDER BY s."name", f."name";
+
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.ROUTINES TO PUBLIC WITH GRANT OPTION;
+
-- The view SEQUENCES contains all sequences defined in the current database.
-- Only those sequences are shown that the current user has access to
-- (by way of being the owner or having some privilege).
@@ -263,7 +373,7 @@ CREATE VIEW INFORMATION_SCHEMA.SEQUENCES
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
