PostgreSQL中从INFORMATION_SCHEMA获取数组类型与维度的方法

问题原因 #

你遇到的报错是因为array_ndims()函数需要接收数组类型的实际数据值，但information_schema.columns里的column_name是sql_identifier类型的字符串（也就是列的名称），不是数组数据本身，直接传递自然会出现类型不匹配的错误。

解决方案 #

根据你的需求，分两种场景处理：

1. 直接从列定义获取数组维度（无需访问表数据）

如果只是想知道列定义时的数组维度（比如integer[]是一维，integer[][]是二维），可以通过解析information_schema.columns的data_type字段来实现，不需要查询表内的实际数据，效率更高：

SELECT
  column_name,
  data_type,
  -- 计算数组维度：统计data_type中[]的数量，每一对[]代表一个维度
  (char_length(data_type) - char_length(replace(data_type, '[]', ''))) / 2 AS defined_array_dimension
FROM information_schema.columns
WHERE
  table_schema = 'public'
  AND table_name = 'sal_emp'
  -- 只筛选数组类型的列
  AND data_type LIKE '%[]';

执行后会返回类似结果：

2. 查询表中实际数据的数组维度（支持动态列名）

如果需要获取表中实际存储的数组维度（PostgreSQL允许同一列的不同行存储不同维度的数组），必须使用动态SQL来将列名字符串转换为实际的列引用。可以通过PL/pgSQL函数实现：

CREATE OR REPLACE FUNCTION get_array_data_dimensions(p_schema text, p_table text)
RETURNS TABLE(column_name text, dimension integer, row_count bigint) AS $$
DECLARE
  col_record record;
BEGIN
  -- 遍历目标表的所有数组列
  FOR col_record IN 
    SELECT column_name 
    FROM information_schema.columns 
    WHERE table_schema = p_schema 
      AND table_name = p_table 
      AND data_type LIKE '%[]'
  LOOP
    -- 动态构建查询，统计该列不同维度的行数
    RETURN QUERY EXECUTE format(
      'SELECT %L AS column_name, 
              array_ndims(%I) AS dimension, 
              COUNT(*) AS row_count 
       FROM %I.%I 
       GROUP BY array_ndims(%I)',
      col_record.column_name,
      col_record.column_name,
      p_schema,
      p_table,
      col_record.column_name
    );
  END LOOP;
END;
$$ LANGUAGE plpgsql;

调用函数获取结果：

SELECT * FROM get_array_data_dimensions('public', 'sal_emp');

这个函数会返回每个数组列中不同维度的分布情况，比如如果pay_by_quarter列有10行一维数组、2行二维数组，就会显示对应的维度和行数。

内容的提问来源于stack exchange，提问作者Dinesh