返回表中的整行和其他表中的列

Question

我正在使用postgresql 14，并试图从表中返回整个记录，除了来自不同表的列。问题是，我不想写记录中的所有标题。我尝试使用这里的指导线[1]，但我得到了不同的错误。谁能告诉我我哪里做错了?

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);但让:

错误:查询结构与函数结果类型不匹配DETAIL:返回的类型整数与列1中期望的类型tbl_licenses不匹配。

Answer 1

您需要将A别名强制转换为正确的记录类型。但是，嵌套的派生表对于其他表来说是不必要的。如果您在SELECT中对license列使用coalesce()，那么您也可以摆脱对临时表的低效创建和更新。

CREATE OR REPLACE FUNCTION get_license_by_id(license_id_ integer)
RETURNS TABLE(rec tbl_licenses, template_name character varying, company_name character varying)
LANGUAGE sql
STABLE
AS $BODY$
SELECT a::tbl_licenses, -- this is important
B.company_name, C.template_name 
FROM (
select license_id, customer_id, ... other columns ..., 
coalesce(license, '1') as license -- makes the temp table unnecessary
from tbl_licenses A
) 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_;
$BODY$
;