fix: load any foreign keys, anyway if the user owns them, on PostgreSQL

ansgarbecker · ansgarbecker · commit dbab4cb0d798 · 2026-02-09T13:47:36.000+01:00
Refs #1653
diff --git a/source/dbconnection.pas b/source/dbconnection.pas
@@ -6544,37 +6544,56 @@ function TPgConnection.GetTableForeignKeys(Table: TDBObject): TForeignKeyList;
   // see #158
   Result := TForeignKeyList.Create(True);
   try
-    ForeignQuery := GetResults('SELECT'+
-      '   refc.constraint_name,'+
-      '   refc.update_rule,'+
-      '   refc.delete_rule,'+
-      '   kcu.table_name,'+
-      '   STRING_AGG(distinct kcu.column_name, '','') AS columns,'+
-      '   ccu.table_schema AS ref_schema,'+
-      '   ccu.table_name AS ref_table,'+
-      '   STRING_AGG(distinct ccu.column_name, '','') AS ref_columns,'+
-      '   STRING_AGG(distinct kcu.ordinal_position::text, '','') AS ord_position'+
-      ' FROM'+
-      '   '+InfSch+'.referential_constraints AS refc,'+
-      '   '+InfSch+'.key_column_usage AS kcu,'+
-      '   '+InfSch+'.constraint_column_usage AS ccu'+
-      ' WHERE'+
-      '   refc.constraint_schema = '+EscapeString(Table.Schema)+
-      '   AND kcu.table_name = '+EscapeString(Table.Name)+
-      '   AND kcu.constraint_name = refc.constraint_name'+
-      '   AND kcu.table_schema = refc.constraint_schema'+
-      '   AND ccu.constraint_name = refc.constraint_name'+
-      '   AND ccu.constraint_schema = refc.constraint_schema'+
-      ' GROUP BY'+
-      '   refc.constraint_name,'+
-      '   refc.update_rule,'+
-      '   refc.delete_rule,'+
-      '   kcu.table_name,'+
-      '   ccu.table_schema,'+
-      '   ccu.table_name'+
-      ' ORDER BY'+
-      '   ord_position'
-      );
+    ForeignQuery := GetResults(
+      'SELECT ' +
+      '    con.conname AS constraint_name, ' +
+      '    CASE con.confupdtype ' +
+      '        WHEN ''a'' THEN ''NO ACTION'' ' +
+      '        WHEN ''r'' THEN ''RESTRICT'' ' +
+      '        WHEN ''c'' THEN ''CASCADE'' ' +
+      '        WHEN ''n'' THEN ''SET NULL'' ' +
+      '        WHEN ''d'' THEN ''SET DEFAULT'' ' +
+      '    END AS update_rule, ' +
+      '    CASE con.confdeltype ' +
+      '        WHEN ''a'' THEN ''NO ACTION'' ' +
+      '        WHEN ''r'' THEN ''RESTRICT'' ' +
+      '        WHEN ''c'' THEN ''CASCADE'' ' +
+      '        WHEN ''n'' THEN ''SET NULL'' ' +
+      '        WHEN ''d'' THEN ''SET DEFAULT'' ' +
+      '    END AS delete_rule, ' +
+      '    src_ns.nspname  AS table_schema, ' +
+      '    src_tbl.relname AS table_name, ' +
+      '    string_agg(src_col.attname, '','' ORDER BY ord.pos) AS columns, ' +
+      '    ref_ns.nspname  AS ref_schema, ' +
+      '    ref_tbl.relname AS ref_table, ' +
+      '    string_agg(ref_col.attname, '','' ORDER BY ord.pos) AS ref_columns, ' +
+      '    string_agg(ord.pos::text, '','' ORDER BY ord.pos)   AS ord_position ' +
+      'FROM pg_constraint con ' +
+      'JOIN pg_class      src_tbl ON src_tbl.oid = con.conrelid ' +
+      'JOIN pg_namespace  src_ns  ON src_ns.oid  = src_tbl.relnamespace ' +
+      'JOIN LATERAL unnest(con.conkey) WITH ORDINALITY AS ord(attnum, pos) ON TRUE ' +
+      'JOIN pg_attribute  src_col ON src_col.attrelid = src_tbl.oid AND src_col.attnum = ord.attnum ' +
+      'JOIN pg_class      ref_tbl ON ref_tbl.oid = con.confrelid ' +
+      'JOIN pg_namespace  ref_ns  ON ref_ns.oid  = ref_tbl.relnamespace ' +
+      'JOIN LATERAL unnest(con.confkey) WITH ORDINALITY AS ref_ord(attnum, pos) ' +
+      '       ON ref_ord.pos = ord.pos ' +
+      'JOIN pg_attribute  ref_col ON ref_col.attrelid = ref_tbl.oid AND ref_col.attnum = ref_ord.attnum ' +
+      'WHERE ' +
+      '    con.contype = ''f'' ' +
+      '    AND src_ns.nspname  = '+EscapeString(Table.Schema) +
+      '    AND src_tbl.relname = '+EscapeString(Table.Name) +
+      'GROUP BY ' +
+      '    con.conname, ' +
+      '    con.confupdtype, ' +
+      '    con.confdeltype, ' +
+      '    src_ns.nspname, ' +
+      '    src_tbl.relname, ' +
+      '    ref_ns.nspname, ' +
+      '    ref_tbl.relname ' +
+      'ORDER BY ' +
+      '    MIN(ord.pos)'
+    );
+
     while not ForeignQuery.Eof do begin
       ForeignKey := TForeignKey.Create(Self);
       Result.Add(ForeignKey);
