如何在PostgreSQL中用函数和存储过程从动态表获取全量数据？

错误原因 #

你遇到的SQL Error [42704]: ERROR: type "xxx" does not exist错误，根源有两个：

• 函数定义里把动态表名参数直接写在了返回类型中：PostgreSQL在创建函数时会解析RETURNS SETOF schemaName."P_DynamicTableName"这类语句，它会把P_DynamicTableName当成一个预定义的复合类型名，而不是你传入的参数值，自然找不到这个类型。
• 用静态SQL引用动态表名：SELECT * FROM schemaName."P_DynamicTableName"会被PostgreSQL当成字面量的表名，不会替换参数的值，同样会导致找不到表（或类型）的问题。

解决方案：使用动态SQL #

要实现动态表查询，必须用EXECUTE执行动态生成的SQL语句，同时用format函数安全处理标识符（避免SQL注入和大小写问题）。以下是针对你两个场景的修正代码：

场景1：带ID过滤的动态表查询 #

CREATE OR REPLACE FUNCTION schemaName."GetAllDataFromDynamicTable"(IN P_DynamicTableName text, IN id integer)
    RETURNS SETOF record
AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT * FROM %I.%I WHERE "Id" = $1',
    'schemaName',
    P_DynamicTableName
  ) USING id;
END;
$$ LANGUAGE plpgsql;

调用方式（需要指定返回列的结构，匹配目标表的字段）：

SELECT * FROM schemaName."GetAllDataFromDynamicTable"('YourTargetTableName', 1) 
AS t("Id" integer, "UserName" text, "CreateTime" timestamp);

场景2：全量获取动态表数据 #

CREATE OR REPLACE FUNCTION schemaName."GetAllDataFromDynamicTable"(tableName character varying)
  RETURNS SETOF record
AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT * FROM %I.%I',
    'schemaName',
    tableName
  );
END;
$$ LANGUAGE plpgsql;

调用方式：

SELECT * FROM schemaName."GetAllDataFromDynamicTable"('YourTargetTableName') 
AS t("Id" integer, "Column1" text, "Column2" numeric);

关键说明 #

• format函数的%I占位符会自动对标识符（schema名、表名）进行转义，处理大小写敏感的问题，同时防止SQL注入。
• USING子句用于传递参数（比如场景1中的id），避免直接拼接参数到SQL字符串中，进一步提升安全性。
• 由于返回的表结构是动态的，使用RETURNS SETOF record作为返回类型，调用时需要通过AS t(...)指定列的结构，匹配目标表的字段。

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