Skip to content

Commit 2bf3184

Browse files
zuinuan123zuinuan123
committed
feat: amend all_cons_columns view
1 parent 7935eb2 commit 2bf3184

3 files changed

Lines changed: 19 additions & 34 deletions

File tree

‎contrib/ivorysql_ora/expected/ora_sysview.out‎

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -217,23 +217,23 @@ CREATE TABLE t_data_types (
217217
c_text TEXT,
218218
CONSTRAINT pk_data_types PRIMARY KEY (c_varchar, c_char)
219219
);
220-
SELECT owner, table_name, constraint_name, column_name, position
220+
SELECT table_name, constraint_name, column_name, position
221221
FROM all_cons_columns
222222
WHERE owner = CURRENT_USER
223223
ORDER BY table_name, constraint_name, position;
224-
owner | table_name | constraint_name | column_name | position
225-
---------+--------------------+-------------------------+-------------+----------
226-
gsx2004 | t_check | chk_age | age | 1
227-
gsx2004 | t_check | chk_salary_range | salary | 1
228-
gsx2004 | t_child | fk_child_parent | pid | 1
229-
gsx2004 | t_data_types | pk_data_types | c_varchar | 1
230-
gsx2004 | t_data_types | pk_data_types | c_char | 2
231-
gsx2004 | t_parent | t_parent_pkey | pid | 1
232-
gsx2004 | t_pk_composite | pk_composite | id1 | 1
233-
gsx2004 | t_pk_composite | pk_composite | id2 | 2
234-
gsx2004 | t_pk_single | t_pk_single_pkey | id | 1
235-
gsx2004 | t_unique | uk_col1 | col1 | 1
236-
gsx2004 | utl_file_directory | utl_file_directory_pkey | dirname | 1
224+
table_name | constraint_name | column_name | position
225+
--------------------+-------------------------+-------------+----------
226+
t_check | chk_age | age | 1
227+
t_check | chk_salary_range | salary | 1
228+
t_child | fk_child_parent | pid | 1
229+
t_data_types | pk_data_types | c_varchar | 1
230+
t_data_types | pk_data_types | c_char | 2
231+
t_parent | t_parent_pkey | pid | 1
232+
t_pk_composite | pk_composite | id1 | 1
233+
t_pk_composite | pk_composite | id2 | 2
234+
t_pk_single | t_pk_single_pkey | id | 1
235+
t_unique | uk_col1 | col1 | 1
236+
utl_file_directory | utl_file_directory_pkey | dirname | 1
237237
(11 rows)
238238

239239
SELECT constraint_name, COUNT(*) AS column_count

‎contrib/ivorysql_ora/sql/ora_sysview.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -131,7 +131,7 @@ CREATE TABLE t_data_types (
131131
CONSTRAINT pk_data_types PRIMARY KEY (c_varchar, c_char)
132132
);
133133

134-
SELECT owner, table_name, constraint_name, column_name, position
134+
SELECT table_name, constraint_name, column_name, position
135135
FROM all_cons_columns
136136
WHERE owner = CURRENT_USER
137137
ORDER BY table_name, constraint_name, position;

‎contrib/ivorysql_ora/src/sysview/sysview--1.0.sql‎

Lines changed: 4 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -1275,35 +1275,19 @@ FROM PG_SETTINGS;
12751275

12761276
CREATE OR REPLACE VIEW SYS.all_cons_columns AS
12771277
SELECT
1278-
pg_authid.rolname::VARCHAR2(128) AS owner, -- 改为表属主
1278+
pg_authid.rolname::VARCHAR2(128) AS owner, -- table owner
12791279
con.conname::VARCHAR2(128) AS constraint_name,
12801280
cls.relname::VARCHAR2(128) AS table_name,
12811281
attr.attname::VARCHAR2(4000) AS column_name,
1282-
pos.pos::NUMBER AS position,
1283-
CASE
1284-
WHEN t.typname = 'varchar' THEN 'VARCHAR2'
1285-
WHEN t.typname = 'bpchar' THEN 'CHAR'
1286-
WHEN t.typname = 'numeric' THEN 'NUMBER'
1287-
WHEN t.typname = 'int2' THEN 'NUMBER'
1288-
WHEN t.typname = 'int4' THEN 'NUMBER'
1289-
WHEN t.typname = 'int8' THEN 'NUMBER'
1290-
WHEN t.typname = 'float4' THEN 'FLOAT'
1291-
WHEN t.typname = 'float8' THEN 'FLOAT'
1292-
WHEN t.typname = 'text' THEN 'CLOB'
1293-
WHEN t.typname = 'date' THEN 'DATE'
1294-
WHEN t.typname = 'timestamp' THEN 'TIMESTAMP'
1295-
WHEN t.typname = 'timestamptz' THEN 'TIMESTAMP WITH TIME ZONE'
1296-
ELSE UPPER(t.typname)
1297-
END AS data_type
1282+
pos.pos::NUMBER AS position
12981283
FROM
12991284
pg_catalog.pg_constraint con
13001285
JOIN pg_catalog.pg_class cls ON con.conrelid = cls.oid
1301-
JOIN pg_catalog.pg_authid ON cls.relowner = pg_authid.oid -- 关联属主
1286+
JOIN pg_catalog.pg_authid ON cls.relowner = pg_authid.oid -- link to table owner
13021287
JOIN pg_catalog.pg_namespace nsp ON cls.relnamespace = nsp.oid
13031288
CROSS JOIN LATERAL unnest(con.conkey) WITH ORDINALITY AS pos(attnum, pos)
13041289
JOIN pg_catalog.pg_attribute attr ON attr.attrelid = cls.oid
13051290
AND attr.attnum = pos.attnum
1306-
JOIN pg_catalog.pg_type t ON attr.atttypid = t.oid
13071291
WHERE
13081292
con.contype IN ('p', 'u', 'f', 'c')
13091293
AND nsp.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
@@ -1314,4 +1298,5 @@ WHERE
13141298
AND has_table_privilege(cls.oid, 'SELECT')
13151299
ORDER BY
13161300
owner, table_name, constraint_name, position;
1301+
13171302
GRANT SELECT ON SYS.all_cons_columns TO PUBLIC;

0 commit comments

Comments
 (0)