Given a table name, how do I extract a list of primary key columns and their datatypes from a plpgsql function?
给定一个表名,如何从plpgsql函数中提取主键列及其数据类型列表?
6 个解决方案
#1
18
The query above is very bad as it is really slow.
上面的查询非常糟糕,因为它非常慢。
I would recommend this official version:
我会推荐这个官方版本:
http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns
http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns
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
15
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
5
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
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
1
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
0
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
18
The query above is very bad as it is really slow.
上面的查询非常糟糕,因为它非常慢。
I would recommend this official version:
我会推荐这个官方版本:
http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns
http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns
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
15
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
5
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
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
1
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
0
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