feat: add SYS.USER_CONS_COLUMNS compatible view

3296442201 · 3296442201 · commit c7aadfa9fc60 · 2026-04-09T15:51:29.000+08:00
diff --git a/contrib/ivorysql_ora/expected/ora_sysview.out b/contrib/ivorysql_ora/expected/ora_sysview.out
@@ -135,5 +135,12 @@ WHERE OBJECT_NAME = 'FUNC_WITH_DEFAULT';
  ARG4          | IN     | 4        | Y
 (5 rows)
 
--- clean data
-DROP FUNCTION IF EXISTS FUNC_WITH_DEFAULT;
+-- USER_CONS_COLUMNS
+CREATE TABLE
+  OWNER   |     TABLE_NAME      |  CONSTRAINT_NAME  | COLUMN_NAME
+----------+---------------------+-------------------+-------------
+ CURRENT_USER | TEST_USER_CONS_COLUMNS | TEST_USER_CONS_COLUMNS_PKEY | ID
+ CURRENT_USER | TEST_USER_CONS_COLUMNS | TEST_USER_CONS_COLUMNS_NOT_NULL | NAME
+(2 rows)
+
+DROP TABLE
diff --git a/contrib/ivorysql_ora/sql/ora_sysview.sql b/contrib/ivorysql_ora/sql/ora_sysview.sql
@@ -82,3 +82,17 @@ SELECT ARGUMENT_NAME, IN_OUT, POSITION, DEFAULTED FROM ALL_ARGUMENTS
 WHERE OBJECT_NAME = 'FUNC_WITH_DEFAULT';
 -- clean data
 DROP FUNCTION IF EXISTS FUNC_WITH_DEFAULT;
+
+-- 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 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
@@ -1192,3 +1192,25 @@ WHERE
   AND PG_GET_USERBYID(C.RELOWNER) = CURRENT_USER;
 GRANT SELECT ON SYS.USER_VIEWS TO PUBLIC;
 
+/* 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
+;
