如何通过plpgsql从Postgres获取表的主键？

Given a table name, how do I extract a list of primary key columns and their datatypes from a plpgsql function?

给定一个表名，如何从plpgsql函数中提取主键列及其数据类型列表？

6 个解决方案

#1

The query above is very bad as it is really slow.

上面的查询非常糟糕，因为它非常慢。

I would recommend this official version:

if schema is needed the query is as follows

如果需要schema，则查询如下

SELECT               
  pg_attribute.attname, 
  format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
FROM pg_index, pg_class, pg_attribute, pg_namespace 
WHERE 
  pg_class.oid = 'foo'::regclass AND 
  indrelid = pg_class.oid AND 
  nspname = 'public' AND 
  pg_class.relnamespace = pg_namespace.oid AND 
  pg_attribute.attrelid = pg_class.oid AND 
  pg_attribute.attnum = any(pg_index.indkey)
 AND indisprimary

#2

To provide a straight bit of SQL, you can list the primary key columns and their types with:

要提供直接的SQL，您可以列出主键列及其类型：

SELECT
c.column_name, c.data_type
FROM
information_schema.table_constraints tc 
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) 
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
where constraint_type = 'PRIMARY KEY' and tc.table_name = 'mytable';

#3

Take a look at pg_constraint system table. Or information_schema.table_constraints view if you prefer to stick close to the SQL standard.

看一下pg_constraint系统表。或者，如果您希望接近SQL标准，请参阅information_schema.table_constraints视图。

For a complete example connect to a DB using psql with the "-E" option and type \d <some_table> - you'll see the actual queries used in describing a table.

有关完整示例使用带有“-E”选项的psql连接到数据库并键入\ d - 您将看到用于描述表的实际查询。

#4

The following SQL statements works for me:

以下SQL语句适用于我：

SELECT a.attname
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'tablename'::regclass
AND    i.indisprimary;

It is directly taken from here.

它直接取自这里。

#5

Beware of indexes where the column order differs from the table's column order. (i.e. if the primary key used columns 3, 2, and 1)

请注意列顺序与表的列顺序不同的索引。（即，如果主键使用第3列，第2列和第1列）

The following query is much more complex, but returns the columns in the proper order. (Remove the 'indisprimary' clause to get the same info for all indexes on a table)

以下查询要复杂得多，但会以正确的顺序返回列。（删除'indisprimary'子句以获取表中所有索引的相同信息）

WITH ndx_list AS
(
    SELECT pg_index.indexrelid
      FROM pg_index, pg_class
     WHERE pg_class.relname = 'test_indices_table'
       AND pg_class.oid = pg_index.indrelid
       AND pg_index.indisprimary
), ndx_cols AS
(
   SELECT pg_class.relname AS index_name, UNNEST(i.indkey) AS col_ndx, i.indisunique, i.indisprimary
     FROM pg_class, pg_index i
    WHERE pg_class.oid = i.indexrelid
      AND pg_class.oid IN (SELECT indexrelid FROM ndx_list)
)
  SELECT ndx_cols.index_name, ndx_cols.indisunique, ndx_cols.indisprimary,
         a.attname, format_type(a.atttypid, a.atttypmod), a.attnum
    FROM pg_class c, pg_attribute a
    JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx)
   WHERE c.oid = 'test_indices_table'::regclass
     AND a.attrelid = c.oid

#6

Preserving column order using generate_subscripts:

使用generate_subscripts保留列顺序：

SELECT
  a.attname,
  format_type(a.atttypid, a.atttypmod) 
FROM
  pg_attribute a
  JOIN (SELECT *, GENERATE_SUBSCRIPTS(indkey, 1) AS indkey_subscript FROM pg_index) AS i
    ON
      i.indisprimary
      AND i.indrelid = a.attrelid
      AND a.attnum = i.indkey[i.indkey_subscript]
WHERE
  a.attrelid = 'your_table'::regclass
ORDER BY
  i.indkey_subscript

#1