Changeset: f9ff163befca for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/f9ff163befca
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/91_information_schema.sql
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
Branch: default
Log Message:
Add creation of view INFORMATION_SCHEMA.PARAMETERS.
Also simplified the logic for INTERVAL_TYPE of view INFORMATION_SCHEMA.COLUMNS.
diffs (truncated from 714 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
@@ -6149,10 +6149,7 @@ sql_update_default(Client c, mvc *sql, s
" 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(CASE c.\"type\" WHEN 'day_interval' THEN 'interval day'
WHEN 'month_interval' THEN (CASE c.\"type_digits\" WHEN 1 THEN 'interval year'
WHEN 2 THEN 'interval year to month' WHEN 3 THEN 'interval month' ELSE NULL
END)"
- " WHEN 'sec_interval' THEN (CASE c.\"type_digits\" 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' ELSE NULL END) ELSE NULL END
AS varchar(40)) AS INTERVAL_TYPE,\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(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"
@@ -6263,7 +6260,7 @@ sql_update_default(Client c, mvc *sql, s
"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(f.\"name\"||'('||f.\"id\"||')' AS varchar(270)) 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"
@@ -6365,6 +6362,60 @@ sql_update_default(Client c, mvc *sql, s
" ORDER BY s.\"name\", f.\"name\";\n"
"GRANT SELECT ON TABLE INFORMATION_SCHEMA.ROUTINES TO PUBLIC
WITH GRANT OPTION;\n"
+ "CREATE VIEW INFORMATION_SCHEMA.PARAMETERS AS SELECT\n"
+ " cast(NULL AS varchar(1)) AS SPECIFIC_CATALOG,\n"
+ " s.\"name\" AS SPECIFIC_SCHEMA,\n"
+ " cast(f.\"name\"||'('||f.\"id\"||')' AS varchar(270)) AS
SPECIFIC_NAME,\n"
+ " 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,\n"
+ " cast(sys.ifthenelse(a.\"inout\" = 0, 'OUT',
sys.ifthenelse(a.\"inout\" = 1, 'IN', 'INOUT')) as varchar(5)) AS
PARAMETER_MODE,\n"
+ " cast(sys.ifthenelse(a.\"inout\" = 0, 'YES', 'NO') as
varchar(3)) AS IS_RESULT,\n"
+ " cast(NULL AS varchar(1)) AS AS_LOCATOR,\n"
+ " a.\"name\" AS PARAMETER_NAME,\n"
+ " cast(NULL AS varchar(1)) AS FROM_SQL_SPECIFIC_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS FROM_SQL_SPECIFIC_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS FROM_SQL_SPECIFIC_NAME,\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(sys.\"describe_type\"(a.\"type\", a.\"type_digits\",
a.\"type_scale\") 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"
+ " cast(sys.ifthenelse(a.\"type\" IN
('varchar','clob','char','json','url','xml'), 'UTF-8', NULL) AS varchar(16)) 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'),
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(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"
+ " cast(NULL AS varchar(1)) AS 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 varchar(1)) AS 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 PARAMETER_DEFAULT,\n"
+ " f.\"schema_id\" AS schema_id,\n"
+ " f.\"id\" AS function_id,\n"
+ " a.\"id\" AS arg_id,\n"
+ " f.\"name\" AS function_name,\n"
+ " f.\"type\" AS function_type,\n"
+ " f.\"system\" AS is_system\n"
+ " FROM sys.\"args\" a\n"
+ " 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.\"func_id\"\n"
+ " INNER JOIN sys.\"schemas\" s ON s.\"id\" = f.\"schema_id\"\n"
+ " ORDER BY s.\"name\", f.\"name\", f.\"id\", a.\"inout\" DESC,
a.\"number\";\n"
+ "GRANT SELECT ON TABLE INFORMATION_SCHEMA.PARAMETERS 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"
@@ -6394,7 +6445,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','routines','schemata','sequences','referential_constraints','table_constraints','tables','views');\n";
+ " and name in
('character_sets','check_constraints','columns','parameters','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
@@ -35,8 +35,8 @@ CREATE VIEW INFORMATION_SCHEMA.CHARACTER
GRANT SELECT ON TABLE INFORMATION_SCHEMA.CHARACTER_SETS TO PUBLIC WITH GRANT
OPTION;
--- The view SCHEMATA contains all schemas in the database that the
--- current user has access to (by way of being the owner or having some
privilege).
+-- The view SCHEMATA contains all schemas in the database
+-- TODO: that the current user has access to (by way of being the owner or
having some privilege).
CREATE VIEW INFORMATION_SCHEMA.SCHEMATA AS SELECT
cast(NULL AS varchar(1)) AS CATALOG_NAME,
s."name" AS SCHEMA_NAME,
@@ -57,7 +57,7 @@ CREATE VIEW INFORMATION_SCHEMA.SCHEMATA
GRANT SELECT ON TABLE INFORMATION_SCHEMA.SCHEMATA TO PUBLIC WITH GRANT OPTION;
-- The view TABLES contains all tables and views defined in the database.
--- Only those tables and views are shown that the current user has access to
+-- TODO: Only those tables and views are shown that the current user has
access to
-- (by way of being the owner or having some privilege).
CREATE VIEW INFORMATION_SCHEMA.TABLES AS SELECT
cast(NULL AS varchar(1)) AS TABLE_CATALOG,
@@ -91,7 +91,7 @@ CREATE VIEW INFORMATION_SCHEMA.TABLES AS
GRANT SELECT ON TABLE INFORMATION_SCHEMA.TABLES TO PUBLIC WITH GRANT OPTION;
-- The view VIEWS contains all views defined in the database.
--- Only those views are shown that the current user has access to
+-- TODO: Only those views are shown that the current user has access to
-- (by way of being the owner or having some privilege).
CREATE VIEW INFORMATION_SCHEMA.VIEWS AS SELECT
cast(NULL AS varchar(1)) AS TABLE_CATALOG,
@@ -119,7 +119,7 @@ CREATE VIEW INFORMATION_SCHEMA.VIEWS AS
GRANT SELECT ON TABLE INFORMATION_SCHEMA.VIEWS TO PUBLIC WITH GRANT OPTION;
-- The view COLUMNS contains information about all table columns (or view
columns) in the database.
--- Only those columns are shown that the current user has access to
+-- TODO: Only those columns are shown that the current user has access to
-- (by way of being the owner or having some privilege).
CREATE VIEW INFORMATION_SCHEMA.COLUMNS AS SELECT
cast(NULL AS varchar(1)) AS TABLE_CATALOG,
@@ -136,7 +136,7 @@ CREATE VIEW INFORMATION_SCHEMA.COLUMNS A
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(CASE c."type" WHEN 'day_interval' THEN 'interval day' WHEN
'month_interval' THEN (CASE c."type_digits" WHEN 1 THEN 'interval year' WHEN 2
THEN 'interval year to month' WHEN 3 THEN 'interval month' ELSE NULL END) WHEN
'sec_interval' THEN (CASE c."type_digits" 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' ELSE NULL END) ELSE NULL END
AS varchar(40)) AS INTERVAL_TYPE,
+ 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,
@@ -201,7 +201,7 @@ CREATE VIEW INFORMATION_SCHEMA.CHECK_CON
GRANT SELECT ON TABLE INFORMATION_SCHEMA.CHECK_CONSTRAINTS TO PUBLIC WITH
GRANT OPTION;
-- The view TABLE_CONSTRAINTS contains all constraints belonging to tables
--- that the current user owns or has some privilege other than SELECT on.
+-- TODO: that the current user owns or has some privilege other than SELECT on.
CREATE VIEW INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS SELECT
cast(NULL AS varchar(1)) AS CONSTRAINT_CATALOG,
s."name" AS CONSTRAINT_SCHEMA,
@@ -227,7 +227,7 @@ CREATE VIEW INFORMATION_SCHEMA.TABLE_CON
GRANT SELECT ON TABLE INFORMATION_SCHEMA.TABLE_CONSTRAINTS TO PUBLIC WITH
GRANT OPTION;
-- The view REFERENTIAL_CONSTRAINTS contains all referential (foreign key)
constraints in the current database.
--- Only those constraints are shown for which the current user has write
access to the referencing table
+-- TODO: Only those constraints are shown for which the current user has write
access to the referencing table
-- (by way of being the owner or having some privilege other than SELECT).
CREATE VIEW INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS SELECT
cast(NULL AS varchar(1)) AS CONSTRAINT_CATALOG,
@@ -259,12 +259,12 @@ 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
+-- TODO: 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(f."name"||'('||f."id"||')' AS varchar(270)) 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,
@@ -363,13 +363,73 @@ CREATE VIEW INFORMATION_SCHEMA.ROUTINES
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
+ WHERE f."type" in (1, 2, 5, 7) -- 1=Scalar function, 2=Procedure, 5=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 PARAMETERS contains information about the parameters (arguments) of
+-- all ROUTINES (functions and procedures) in the current database.
+-- TODO: Only those routine parameters are shown that the current user has
+-- access to (by way of being the owner or having some privilege).
+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, -- TODO:
replace with full routine signature string. Note
sys.fully_qualified_functions.nme does not produce the correct signature.
+ 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, -- we do not yet support
INOUT
+ 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,
+ -- MonetDB column extensions
+ 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."func_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";
+
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.PARAMETERS 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
+-- TODO: Only those sequences are shown that the current user has access to
-- (by way of being the owner or having some privilege).
CREATE VIEW INFORMATION_SCHEMA.SEQUENCES AS SELECT
cast(NULL AS varchar(1)) AS SEQUENCE_CATALOG,
@@ -399,3 +459,4 @@ CREATE VIEW INFORMATION_SCHEMA.SEQUENCES
ORDER BY s."name", sq."name";
GRANT SELECT ON TABLE INFORMATION_SCHEMA.SEQUENCES TO PUBLIC WITH GRANT OPTION;
+
diff --git a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
--- a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
+++ b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
@@ -102,6 +102,9 @@ args
ids
DEP_VIEW
args
+parameters
+DEP_VIEW
+args
routines
DEP_VIEW
auths
@@ -402,6 +405,9 @@ functions
ids
DEP_VIEW
functions
+parameters
+DEP_VIEW
+functions
routines
DEP_VIEW
ids
@@ -588,6 +594,9 @@ schemas
ids
DEP_VIEW
schemas
+parameters
+DEP_VIEW
+schemas
referential_constraints
DEP_VIEW
schemas
@@ -871,7 +880,7 @@ DEP_FUNC
query TTT rowsort
SELECT distinct c.name, v.name, 'DEP_VIEW' from sys.columns as c, sys.tables
as v, sys.dependencies as dep where c.id = dep.id AND v.id = dep.depend_id AND
dep.depend_type = 5 AND v.type in (1, 11, 21, 31) order by c.name, v.name
----
-1770 values hashing to 5410dd601c567b97e738aeee54fab229
+1821 values hashing to 4841f015a85929eed99bb2099a4e17b8
query TTT rowsort
SELECT c.name, k.name, 'DEP_KEY' from sys.columns as c, sys.objects as kc,
sys.keys as k where kc."name" = c.name AND kc.id = k.id AND k.table_id =
c.table_id AND k.rkey = -1 order by c.name, k.name
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
