编写基于表名和ID进行查询的PostgreSQL函数

需求说明 #

• 编写可被应用调用的函数，返回关联查询的完整结果集
• 结果需包含Users表的所有列，以及由输入变量指定的关联表的所有列
• 根据传入的UUID类型用户ID筛选结果

现有代码问题 #

用户提供的代码存在以下关键问题：

• 硬编码了用户ID值，未使用传入的User_ID参数
• 错误将字符串"Table_Name"赋值给变量，未实际引用传入的关联表名参数
• 静态引用表名public._tablename，未处理动态表名的逻辑
• 未正确定义临时表tmp_table，且返回集合的方式不符合PL/pgSQL规范

create or replace function DynamicTableSearch(Table_Name varchar,User_ID uuid)
returns setof tmp_table as $$

declare _userid uuid;
declare _tablename varchar;
result tmp_table;
BEGIN
    SELECT 'efaad10a-6e0b-4b52-8d55-2c4eefd84cd5' INTO _userid;
    select '"Table_Name"' into _tablename;

    --DROP TABLE IF EXISTS tmp_table;
    --CREATE TEMPORARY TABLE tmp_table AS
    SELECT * FROM public."Users" u
    join public._tablename x on u."id" = x."UsersID"
    into tmp_table
    WHERE   id = _userid;
return tmp_table
 --RETURN tmp_table;
END $$ language plpgsql

修正后的代码 #

以下是符合需求的实现，使用动态SQL处理关联表名，同时避免SQL注入风险：

CREATE OR REPLACE FUNCTION DynamicTableSearch(p_table_name varchar, p_user_id uuid)
RETURNS SETOF RECORD AS $$
BEGIN
    -- 动态拼接SQL，使用USING传递参数避免注入
    RETURN QUERY EXECUTE format(
        'SELECT u.*, x.* 
         FROM public."Users" u 
         JOIN public.%I x ON u."id" = x."UsersID" 
         WHERE u."id" = $1',
        p_table_name
    ) USING p_user_id;
END;
$$ LANGUAGE plpgsql;

使用说明 #

调用该函数时，需要明确指定返回的列结构（因为返回的是RECORD类型），示例：

SELECT * FROM DynamicTableSearch('关联表名', '你的用户UUID'::uuid) 
AS (
    -- 这里列出Users表的所有列，以及关联表的所有列
    id uuid,
    username varchar,
    email varchar,
    -- ... Users表其他列
    关联表列1 varchar,
    关联表列2 int,
    -- ... 关联表其他列
);

如果希望更易用，可以提前创建一个复合类型，或者使用RETURNS TABLE，但由于关联表是动态的，RETURNS SETOF RECORD是最灵活的方式。

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