PostgreSQL函数返回多列：批量查询行元数据列方案需求

为什么不能用SELECT * #

• SELECT *会返回表中所有列，无法精准只选择你需要的业务列+元数据列，容易引入冗余数据
• 表结构变更（新增/删除列）会导致SELECT *的返回结果自动变化，可能破坏下游依赖的逻辑，稳定性差
• 多余列会增加数据传输量，影响查询性能

针对不同数据库的实现方案 #

PostgreSQL #

方式1：psql客户端宏（最简便）

在psql中预定义元数据列的宏，后续直接引用：

-- 定义宏，把所有元数据列写在这里
\set default_meta '\"creationTimestamp\", \"createdBy\"'

-- 查询时使用
SELECT "foo", :default_meta FROM "bar";

方式2：表值函数+横向连接

如果需要在SQL语句中直接用类似函数的写法，可以创建表值函数，配合LATERAL连接：

CREATE OR REPLACE FUNCTION get_default_meta(p_row "bar")
RETURNS TABLE(creationTimestamp TIMESTAMP, createdBy VARCHAR) AS $$
BEGIN
  RETURN QUERY SELECT p_row.creationTimestamp, p_row.createdBy;
END;
$$ LANGUAGE plpgsql;

-- 使用方式
SELECT "foo", meta.*
FROM "bar", LATERAL get_default_meta("bar") AS meta;

MySQL #

预处理语句+自定义变量

可以用自定义变量存储元数据列列表，通过预处理语句执行：

SET @default_meta = '`creationTimestamp`, `createdBy`';
SET @sql = CONCAT('SELECT `foo`, ', @default_meta, ' FROM `bar`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

通用方案：应用层封装 #

如果是在应用代码中执行SQL，可以在代码层面封装一个获取元数据列的方法，拼接SQL语句时自动加入这些列，比如（以Python为例）：

def get_default_meta_cols():
    return ["creationTimestamp", "createdBy"]

# 拼接SQL
business_cols = ["foo"]
all_cols = business_cols + get_default_meta_cols()
sql = f"SELECT {', '.join(f'\"{col}\"' for col in all_cols)} FROM \"bar\""

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