postgres: find all integer columns with its current max value in it

Question

How to find all integer typed primary key columns with its current max value in it from all tables from all databases in Postgres instance?

I want to find all the int typed primary key columns from all tables which are nearing to overflow its max value 2147483647.

Answer 1

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;$$;

Then you can get a list with

SELECT * FROM intpkmax();