PostgreSQL 14自定义函数返回整行加其他表列报错求助

我正在使用PostgreSQL 14，想要返回tbl_licenses表的整条记录，同时返回其他关联表的指定列，但不想手动写出该表的所有字段。参考相关指南编写函数后，运行时出现错误，想请教问题出在哪里？

函数代码如下：

CREATE OR REPLACE FUNCTION public.get_license_by_id(license_id_ integer)
    RETURNS TABLE(rec tbl_licenses, template_name character varying, company_name character varying)
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE SECURITY DEFINER PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
BEGIN 
    CREATE TEMPORARY TABLE tempTable AS (
        SELECT 
            (rec).*, B.company_name, C.template_name 
            -- Tried using A instead of (rec).* with error: column "a" has pseudo-type record
        FROM 
        (
            (SELECT * FROM tbl_licenses) A
            LEFT JOIN
            (SELECT * FROM tbl_customers) B on A.customer_id = B.customer_id
            LEFT JOIN
            (SELECT * FROM tbl_templates) C on A.template_id = C.template_id
        )
    );
    UPDATE tempTable
    SET license = '1'
    WHERE tempTable.license IS NOT NULL;
    RETURN QUERY ( 
        SELECT * FROM tempTable
    );
    
    DROP TABLE tempTable;
    RETURN;
END;
$BODY$;

调用语句：SELECT rec FROM get_license_by_id(1);

得到错误：

ERROR: structure of query does not match function result type
DETAIL: Returned type integer does not match expected type tbl_licenses in column 1.

问题分析与解决 #

核心问题 #

你定义的函数返回结构要求第一列是tbl_licenses类型的整条记录，但实际查询时把tbl_licenses的所有字段拆成了单独列返回，导致返回列的类型、数量与定义完全不匹配，触发报错。此外子查询里的(rec).*是无效写法——rec字段从未被定义过。

修正方案 #

方案1：简化逻辑（无需临时表）

如果你的UPDATE只是测试代码，或可以整合到查询中，直接用以下精简实现：

CREATE OR REPLACE FUNCTION public.get_license_by_id(license_id_ integer)
    RETURNS TABLE(rec tbl_licenses, template_name character varying, company_name character varying)
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE SECURITY DEFINER PARALLEL UNSAFE
    ROWS 1000
AS $BODY$
BEGIN 
    RETURN QUERY 
        SELECT 
            A::tbl_licenses AS rec,  -- 将整条记录转为tbl_licenses类型
            C.template_name,
            B.company_name
        FROM tbl_licenses A
        LEFT JOIN tbl_customers B ON A.customer_id = B.customer_id
        LEFT JOIN tbl_templates C ON A.template_id = C.template_id
        WHERE A.license_id = license_id_;  -- 补充参数过滤，原函数未用到入参
END;
$BODY$;

方案2：保留临时表（适配UPDATE需求）

如果确实需要临时表处理数据，要保证临时表结构与函数返回结构严格一致：

CREATE OR REPLACE FUNCTION public.get_license_by_id(license_id_ integer)
    RETURNS TABLE(rec tbl_licenses, template_name character varying, company_name character varying)
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE SECURITY DEFINER PARALLEL UNSAFE
    ROWS 1000
AS $BODY$
BEGIN 
    -- 创建与返回结构匹配的临时表
    CREATE TEMPORARY TABLE tempTable (
        rec tbl_licenses,
        template_name character varying,
        company_name character varying
    );

    -- 插入数据时保留整条记录
    INSERT INTO tempTable
        SELECT 
            A::tbl_licenses,
            C.template_name,
            B.company_name
        FROM tbl_licenses A
        LEFT JOIN tbl_customers B ON A.customer_id = B.customer_id
        LEFT JOIN tbl_templates C ON A.template_id = C.template_id
        WHERE A.license_id = license_id_;

    -- 修改记录中的字段（需通过rec.xxx访问内嵌字段）
    UPDATE tempTable
    SET rec = (rec).* || jsonb_build_object('license', '1')::jsonb::tbl_licenses
    WHERE (rec).license IS NOT NULL;

    RETURN QUERY SELECT * FROM tempTable;
    
    DROP TABLE tempTable;
END;
$BODY$;

调用说明 #

执行以下语句即可正确获取结果：

-- 获取所有返回列
SELECT * FROM get_license_by_id(1);
-- 单独获取整条license记录
SELECT rec FROM get_license_by_id(1);

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