内省 postgresql 8.3 查找外键

Question

我正在尝试内省 postgres 8.3 数据库以检索其详细信息 外键。想象一下我有以下模式：

CREATE TABLE "a" (
 "id" SERIAL PRIMARY KEY
);

CREATE TABLE "b" (
 "one" integer,
 "two" integer,
 "a_id" integer REFERENCES "a",
 PRIMARY KEY ("one", "two")
);

CREATE TABLE "c" (
 "id" SERIAL PRIMARY KEY,
 "a_id" integer REFERENCES "a",
 "b_one" integer,
 "b_two" integer,
 FOREIGN KEY ("b_one", "b_two") REFERENCES "b"
);

然后我想运行一个产生以下结果的查询：

table | columns        | foreign table | foreign columns
--------------------------------------------------------
 b    | {a_id}         | a             | {id}
 c    | {a_id}         | a             | {id}
 c    | {b_one, b_two} | b             | {one, two}

查询

SELECT conrelid::regclass as "table",
       conkey as columns, 
       confrelid::regclass as "foreign table", 
       confkey as "foreign columns"
  FROM pg_constraint
 WHERE contype = 'f' ;

 table | columns | foreign table | foreign columns 
-------+---------+---------------+-----------------
 b     | {3}     | a             | {1}
 c     | {2}     | a             | {1}
 c     | {3,4}   | b             | {1,2}

我的第一次努力已经给了我几乎已经存在的 。但我努力将列号转换为列 名字还没有给我带来想要的结果。谷歌搜索给了我 下面的内容又不太正确。

SELECT conrelid::regclass as "table",
       a.attname as columns,
       confrelid::regclass as "foreign table",
       af.attname as "foreign columns"
  FROM pg_attribute AS af,
       pg_attribute AS a,
       ( SELECT conrelid,
                confrelid,
                conkey[i] AS conkey,
                confkey[i] as confkey
           FROM ( SELECT conrelid,
                         confrelid, 
                         conkey, 
                         confkey, 
                         generate_series(1, array_upper(conkey, 1)) AS i
                    FROM pg_constraint
     WHERE contype = 'f'
         ) AS ss
       ) AS ss2
 WHERE af.attnum = confkey
   AND af.attrelid = confrelid
   AND a.attnum = conkey
   AND a.attrelid = conrelid ;

 table | columns | foreign table | foreign columns 
-------+---------+---------------+-----------------
 b     | a_id    | a             | id
 c     | a_id    | a             | id
 c     | b_one   | b             | one
 c     | b_two   | b             | two

谁能帮我迈出最后一步吗？

Answer 1

充实 Peter Eisentraut 的答案；对于 postgresql 8.3 array_agg
函数可以定义为

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

然后获得我想要的答案的完整查询变成

SELECT "table",
       array_accum(columns) AS columns,
       "foreign table",
       array_accum("foreign columns") AS "foreign columns"
  FROM ( SELECT conrelid::regclass AS "table",
                a.attname as columns,
                confrelid::regclass as "foreign table",
                af.attname as "foreign columns"
           FROM pg_attribute AS af,
                pg_attribute AS a,
                ( SELECT conrelid,
                         confrelid,
                         conkey[i] AS conkey,
                         confkey[i] as confkey
                    FROM ( SELECT conrelid,
                                  confrelid, 
                                  conkey, 
                                  confkey, 
                                  generate_series(1, array_upper(conkey, 1)) AS i
                             FROM pg_constraint
              WHERE contype = 'f'
                  ) AS ss
                ) AS ss2
          WHERE af.attnum = confkey
            AND af.attrelid = confrelid
            AND a.attnum = conkey
            AND a.attrelid = conrelid
       ) AS ss3
  GROUP BY "table",
           "foreign table";

原谅评论他的答案的非标准方式，我仍在学习
如何使用 Stackoverflow，并且首先没有创建帐户
实例并没有帮助事情。

Answer 2

SELECT table, array_agg(columns), foreign_table, array_agg(foreign_columns) FROM (your query here) GROUP BY table, foreign_table;

array_agg 需要 PostgreSQL 8.4。对于早期版本，您可以定义自己的（在文档中查找 array_accum）。显然，您可以将此查询合并到您的大查询中，但这应该给您一个总体思路。