如何从所有表中选择所有列，其最大值来自 Redshift 中的 1 个架构？

Question

我有一个大约有300个表的模式，需要找到每个表中每列的最大值，假设我有表"人"；具有列："；name"姓"passport_number"；以及表"；个人电话"具有列："；phone_ number"使用年限"；，结果应该是这样的：

husina>9999992349890年

table	column	max_value
人员	姓名	Martisj
个人	姓氏
人员	护照号码
Person_phone	phone_number	[phone]
个人电话	使用

以下是执行此操作的过程的代码。

CREATE OR REPLACE PROCEDURE max_of_all_columns(schema_name TEXT)
AS
$$
DECLARE
ro    RECORD;
max_r RECORD;
BEGIN
DROP TABLE IF EXISTS tmp_xyz;
CREATE TEMP TABLE tmp_xyz
(
table_name  VARCHAR(100),
column_name VARCHAR(100),
max_val     TEXT
);

FOR ro IN SELECT
QUOTE_IDENT(table_name) :: VARCHAR                                 AS table_name,
QUOTE_IDENT(column_name) :: VARCHAR                                AS column_name,
'SELECT MAX(' || QUOTE_IDENT(column_name) || ') :: TEXT AS max_val FROM ' || QUOTE_IDENT(table_catalog) ||
'.' || QUOTE_IDENT(table_schema) || '.' || QUOTE_IDENT(table_name) AS max_query
FROM
information_schema.columns c
WHERE
table_schema = schema_name AND data_type IN (
-- add more types if necessary
'bigint',
'character',
'character varying',
'date',
'double precision',
'integer',
'numeric',
'real',
'smallint',
'timestamp without time zone'
)
LOOP
EXECUTE ro.max_query INTO max_r;
INSERT INTO tmp_xyz(table_name, column_name, max_val)
VALUES (ro.table_name, ro.column_name, max_r.max_val);
END LOOP;
END
$$ LANGUAGE plpgsql;

让我们创建一些测试表。

-- create schema for testing.
CREATE SCHEMA IF NOT EXISTS trash;
-- add test tables. 
DROP TABLE IF EXISTS trash.tmp_xyz;
CREATE TABLE trash.tmp_xyz AS
SELECT 1::INT AS abc, 45::INT AS xyz UNION ALL SELECT 2::INT AS abc, 24::INT AS xyz;
DROP TABLE IF EXISTS trash.tmp_abc;
CREATE TABLE trash.tmp_abc AS
SELECT 1::INT AS abc, 'Maartin'::VARCHAR AS xyz_varchar UNION ALL SELECT 2::INT AS abc, 'Khusia'::VARCHAR AS xyz_varchar;

现在让我们执行该过程。

CALL max_of_all_columns('trash');
SELECT * FROM tmp_xyz;

结果如下：

|table_name|column_name|max_val|
+----------+-----------+-------+
|tmp_abc   |abc        |2      |
|tmp_abc   |xyz_varchar|Maartin|
|tmp_xyz   |abc        |2      |
|tmp_xyz   |xyz        |45     |
+----------+-----------+-------+