gpt4 book ai didi

sql - Postgres : find all integer columns with its current max value in it

转载 作者:行者123 更新时间:2023-11-29 12:09:44 27 4
gpt4 key购买 nike

如何从 Postgres 实例中所有数据库的所有表中找到所有整数类型的主键列及其当前最大值?

我想从所有接近溢出其最大值 2147483647 的表中找到所有 int 类型的主键列。

最佳答案

CREATE OR REPLACE FUNCTION intpkmax() RETURNS
TABLE(schema_name name, table_name name, column_name name, max_value integer)
LANGUAGE plpgsql STABLE AS
$$BEGIN
/* loop through tables with a simgle integer column as primary key */
FOR schema_name, table_name, column_name IN
SELECT sch.nspname, tab.relname, col.attname
FROM pg_class tab
JOIN pg_constraint con ON con.conrelid = tab.oid
JOIN pg_attribute col ON col.attrelid = tab.oid
JOIN pg_namespace sch ON sch.oid = tab.relnamespace
WHERE con.contype = 'p'
AND array_length(con.conkey, 1) = 1
AND col.atttypid = 'integer'::regtype
AND NOT col.attisdropped
LOOP
/* get the maximum value of the primary key column */
EXECUTE 'SELECT max(' || quote_ident(column_name) ||
') FROM ' || quote_ident(schema_name) ||
'.' || quote_ident(table_name) || ''
INTO max_value;
/* return the next result */
RETURN NEXT;
END LOOP;
END;$$;

然后你可以得到一个列表

SELECT * FROM intpkmax();

关于sql - Postgres : find all integer columns with its current max value in it,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/42712604/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com