PostgreSQL查询列名匹配指定字符串的行时出错求助

错误根源 #

你的查询报错是因为SELECT列表中用作表达式的子查询必须返回且仅返回1行结果，但你的子查询查出了多个符合AB%规则的列名（多行数据），数据库无法将多行结果作为单个字段的值处理，因此抛出ERROR: more than one row returned by a subquery used as an expression错误。

正确实现方式 #

要查询表中所有列名以AB开头的列的所有行数据，由于列名是动态匹配的，需要使用PostgreSQL的动态SQL来构造查询语句，以下是两种可行方案：

方案1：使用DO块执行动态查询 #

DO $$
DECLARE
  cols text;
BEGIN
  -- 拼接所有匹配的列名，用逗号分隔
  SELECT string_agg(column_name, ', ')
  INTO cols
  FROM information_schema.columns 
  WHERE table_schema = 'myschema'
    AND table_name = 'mytable' 
    AND column_name LIKE 'AB%';

  -- 执行动态构造的查询
  EXECUTE format('SELECT %s FROM myschema.mytable', cols);
END $$;

方案2：创建函数返回查询结果 #

如果需要直接获取查询结果（而非仅执行），可以创建一个PL/pgSQL函数：

CREATE OR REPLACE FUNCTION get_ab_columns()
RETURNS SETOF record AS $$
DECLARE
  cols text;
BEGIN
  SELECT string_agg(column_name, ', ')
  INTO cols
  FROM information_schema.columns 
  WHERE table_schema = 'myschema'
    AND table_name = 'mytable' 
    AND column_name LIKE 'AB%';

  RETURN QUERY EXECUTE format('SELECT %s FROM myschema.mytable', cols);
END $$ LANGUAGE plpgsql;

调用函数时需要指定返回的列结构（替换为实际列名和类型）：

SELECT * FROM get_ab_columns() AS (ab_col1 varchar, ab_col2 int, ...);

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