Postgresql: How to work around pseudo-type record errors

Question

Pseudo-record errors have been a fairly common problem, the problem is convincing Postgres it has a working rowset. There are times it might be easier to work with an intermediary recordtype that looks like a pseudo-record but serves as a fully materialized recordset, and without creating & casting to a formal "type" [record type].

/*
-- This wont work, causes pseudo-type record error
*/
CREATE TEMP TABLE tmpErrPseudoSave AS
SELECT ROW( col2, col1 ) AS anonrow FROM tmpOrigDat ; 

Answer 1

As of PostgreSql Ver. 13 there is a decent work-around. This is accomplished by encapsulating the returning set as a inner-query CTE, that the outer query can see & use it as a fully materialized state.

/**
   we can save it however if we just create the anon row
   but re-expand it right away saving the fields as generic f1, f2 ....
   ( renames every field automatically )
*/

CREATE TEMP TABLE tmpPseudoSave AS
SELECT (rowrow).* -- expand the anon table struct as f1, f2 .... 
FROM /** s1 is fully materialized **/
 ( SELECT  ROW( (zz).* ) rowrow FROM (SELECT * FROM  tmpOrigDat ) zz ) s1 
;

It's even possible to cast the rowtype to a new, compatible type, on-the-fly:

DROP TABLE IF EXISTS tmpNewRowType;
CREATE TEMP TABLE tmpNewRowType AS 
  SELECT NULL::CHAR(1) AS zCol1newname2  
        ,NULL::INT AS zCol2newname1 
        LIMIT 0; /** empty object **/
SELECT * FROM tmpNewRowType ; 


SELECT  ((r)::tmpNewRowType).* 
FROM ( SELECT * FROM tmpPseudoSave ) r /** needs to be encapsulated as a CTE subselect (r)**/
;

Such column aliasing could be table-driven from a data dictionary, using an anonymous DO LOOP to build matching column aliases, in their correct matching ordinal positions.

PRIOR TO PostgreSql 13, saving the intermediary pseudo-typed row-type isn't possible, you'll have to cast to a pre-defined row-type (stored either as a formal Postgres type, table, or temp table).

CREATE TEMP TABLE tmpFinalSave AS
SELECT ( s1::tmpNewRowType ).*
FROM   ( SELECT * FROM  tmpOrigDat ) s1 ; 

zcol1newname2   zcol2newname1
            a               1
            b               2
            c               3
            d               4
            e               5

FULL demo at dbfiddle.uk