Return entire row from table and columns from other tables-CodePudding

I'm using postgresql 14 and trying to return an entire record from a table in addition to columns from different tables. The catch is, that I don't want to write all the titles in the record. I tried working with the guide lines here [1], but I'm getting different errors. Can anyone tell me what I'm doing wrong?

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$;

I'm calling the function like SELECT rec FROM get_license_by_id(1); but getting:

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

CodePudding user response：

You need to cast the A alias to the correct record type. However the nested derived tables are not necessary for the other tables. If you use a coalesce() for the license column in the SELECT, then you get get rid of the inefficient creation and update of the temp table as well.

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$
;