Add SYS.USER_CONS_COLUMNS view and test case

3296442201 · 3296442201 · commit ac98e243e01e · 2026-04-10T20:24:28.000+08:00
diff --git a/contrib/ivorysql_ora/expected/ora_sysview.out b/contrib/ivorysql_ora/expected/ora_sysview.out
@@ -181,3 +181,13 @@ ORDER BY NAME;
  listen_addresses | 2    |                        | localhost     | FALSE     | FALSE            | FALSE            | Sets the host name or IP address(es) to listen to.
 (5 rows)
 
+-- USER_CONS_COLUMNS
+CREATE TABLE
+  OWNER   |     TABLE_NAME      |  CONSTRAINT_NAME  | COLUMN_NAME
+----------+---------------------+-------------------+-------------
+ WZL | TEST_USER_CONS_COLUMNS | TEST_USER_CONS_COLUMNS_PKEY | ID
+ WZL | TEST_USER_CONS_COLUMNS | TEST_USER_CONS_COLUMNS_NOT_NULL | NAME
+ WZL | TEST_USER_CONS_COLUMNS | TEST_USER_CONS_COLUMNS_NOT_NULL | ID
+(3 rows)
+
+DROP TABLE
diff --git a/contrib/ivorysql_ora/sql/ora_sysview.sql b/contrib/ivorysql_ora/sql/ora_sysview.sql
@@ -90,3 +90,17 @@ SELECT *
 FROM SYS.V$PARAMETER 
 WHERE NAME IN ('listen_addresses','application_name','archive_command','archive_mode','block_size')
 ORDER BY NAME;
+
+-- USER_CONS_COLUMNS
+CREATE TABLE TEST_USER_CONS_COLUMNS (
+    ID INT PRIMARY KEY,
+    NAME VARCHAR2(30) NOT NULL
+);
+
+SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME
+FROM SYS.USER_CONS_COLUMNS
+WHERE TABLE_NAME = 'TEST_USER_CONS_COLUMNS'
+ORDER BY CONSTRAINT_NAME, COLUMN_NAME;
+
+-- Clean
+DROP TABLE IF EXISTS TEST_USER_CONS_COLUMNS;
diff --git a/contrib/ivorysql_ora/src/sysview/sysview--1.0.sql b/contrib/ivorysql_ora/src/sysview/sysview--1.0.sql
@@ -1273,3 +1273,26 @@ SELECT
     SHORT_DESC::VARCHAR2(255) AS DESCRIPTION
 FROM PG_SETTINGS;
 
+/* USER_CONS_COLUMNS */
+CREATE OR REPLACE VIEW SYS.USER_CONS_COLUMNS AS
+	SELECT
+		SYS.ORA_CASE_TRANS(PG_GET_USERBYID(C.RELOWNER)::VARCHAR2)::VARCHAR2(128) AS OWNER,
+		SYS.ORA_CASE_TRANS(C.RELNAME::VARCHAR2)::VARCHAR2(128) AS TABLE_NAME,
+		SYS.ORA_CASE_TRANS(CON.CONNAME::VARCHAR2)::VARCHAR2(128) AS CONSTRAINT_NAME,
+		SYS.ORA_CASE_TRANS(ATT.ATTNAME::VARCHAR2)::VARCHAR2(128) AS COLUMN_NAME
+	FROM
+		PG_CONSTRAINT AS CON
+	JOIN PG_CLASS AS C
+		ON CON.CONRELID = C.OID
+	JOIN PG_ATTRIBUTE AS ATT
+		ON ATT.ATTRELID = C.OID
+		AND ATT.ATTNUM = ANY(CON.CONKEY)
+	WHERE
+		C.RELOWNER::REGROLE = CURRENT_USER::REGROLE
+		AND C.RELNAMESPACE != 'PG_CATALOG'::REGNAMESPACE::OID
+		AND C.RELNAMESPACE != 'PG_TOAST'::REGNAMESPACE::OID
+		AND C.RELNAMESPACE != 'INFORMATION_SCHEMA'::REGNAMESPACE::OID
+ORDER BY
+	TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME
+; 
+
