如何通过plpgsql从Postgres获取表的主键? [英] How do I get the primary key(s) of a table from Postgres via plpgsql?

问题描述

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

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

推荐答案

上面的查询非常慢，因为它确实很慢.

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

我会推荐此正式版本:

http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns

如果需要模式，查询如下

if schema is needed the query is as follows

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

这篇关于如何通过plpgsql从Postgres获取表的主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！