SQL函数内无法创建临时表？求原因及替代方案

你遇到的“临时表不存在”错误，核心原因是你的函数是SQL语言类型（LANGUAGE 'sql'），但内部混合使用了PL/pgSQL的匿名DO块，导致临时表的作用域和解析逻辑冲突：

• SQL函数在执行前会对所有语句做完整的语法和语义校验，此时临时表ROSTER_TABLE还未被创建，后续引用该表的CTE和INSERT语句在解析阶段就会判定“表不存在”。
• 单独执行代码时是逐句执行，每一步执行完成后表已经存在，后续语句解析时能找到表，所以不会报错。
• 另外，DO块是一个独立的执行单元，虽然它能访问外部创建的临时表，但SQL函数的全局解析逻辑不会考虑DO块内的操作对临时表的修改，依然会在预解析阶段报错。

方案1：将函数改为PL/pgSQL语言 #

PL/pgSQL是过程化语言，会按语句顺序逐行执行，不会在执行前预校验所有语句的对象存在性，完美适配你的循环逻辑。修改后的函数框架如下：

CREATE OR REPLACE FUNCTION api."post_publish_Roster"()
    RETURNS void
    LANGUAGE plpgsql -- 修改语言类型
AS $BODY$
DECLARE
   weekstart INTEGER;
   weekend INTEGER ;
BEGIN
    -- 删除临时表（如果存在）
    DROP TABLE IF EXISTS ROSTER_TABLE;

    -- 创建临时表
    CREATE TEMP TABLE ROSTER_TABLE AS
    SELECT ROSTER_ID,
        LINK_ID,
        PAYNUMBER,
        USERNAME,
        LINE_POSITION,
        CREWNAME,
        WEEKNUMBER,
        WEEKSTARTDATE,
        WEEKENDDATE
    FROM CREW_LINKS.LINKS_MAP
    CROSS JOIN LATERAL GET_WEEKS('2023-02-12','2023-03-04') AS WEEKDATA
    WHERE ROSTER_ID = 234
        AND WEEKDATA.WEEKNUMBER in
            (SELECT MIN(WEEKNUMBER)
                FROM GET_WEEKS('2023-02-12','2023-03-04'));

    -- 原DO块逻辑直接移到此处，无需嵌套DO块
    select min(weeknumber) into weekstart  from  get_weeks('2023-02-12', '2023-03-04');
    select max(weeknumber) into weekend  from  get_weeks('2023-02-12', '2023-03-04') ;

    WHILE weekstart < weekend LOOP
        INSERT INTO roster_table
        SELECT roster_id, link_id, paynumber, username, line_position+1 AS line_position ,  crewname,rt.weeknumber+1 AS weeknumber
                ,w.weekstartdate,w.weekenddate
                FROM roster_table rt
                INNER JOIN
                (select  * from  get_weeks('2023-02-12', '2023-03-04'))w
                ON w.weeknumber=rt.weeknumber+1
                WHERE rt.weeknumber=weekstart;

        update roster_table rw
        set line_position=(select min(line_position) from roster_table )
        where weeknumber=weekstart+1 and line_position =(select MAX(line_position) from roster_table ) ;

        weekstart := weekstart + 1;
    END LOOP;

    -- 后续CTE与INSERT语句直接放在此处
    WITH COMBIN AS
        (SELECT R.DEPOT,
                R.GRADE,
                R.VALID_FROM,
                R.VALID_TO,
                RD.ROWNUMBER,
                RD.SUNDAY,
                RD.MONDAY,
                RD.TUESDAY,
                RD.WEDNESDAY,
                RD.THURSDAY,
                RD.FRIDAY,
                RD.SATURDAY,
                RD.TOT_DURATION
            FROM CREW_ROSTER.ROSTER_NAME R
            JOIN CREW_ROSTER.DRAFT RD ON R.R_ID = RD.R_ID
            WHERE R.R_ID = 234),
        div AS
        (SELECT DEPOT,
                GRADE,
                VALID_FROM,
                VALID_TO,
                ROWNUMBER,
                UNNEST('{sunday,
    monday,
    tuesday,
    wednesday,
    thursday,
    friday,
    saturday }'::text[]) AS COL,
                UNNEST(ARRAY[ SUNDAY :: JSON,

                    MONDAY :: JSON,
                    TUESDAY :: JSON,
                    WEDNESDAY :: JSON,
                    THURSDAY :: JSON,
                    FRIDAY :: JSON,
                    SATURDAY:: JSON]) AS COL1
            FROM COMBIN),
        DAY AS
        (SELECT date::date,
                TRIM (BOTH TO_CHAR(date, 'day'))AS DAY
            FROM GENERATE_SERIES(date '2023-02-12', date '2023-03-04',interval '1 day') AS T(date)), FINAL AS
        (SELECT *
            FROM div C
            JOIN DAY D ON D.DAY = C.COL
            ORDER BY date,ROWNUMBER ASC), TT1 AS
        (SELECT ROWNUMBER,date,COL,
                (C ->> 'dia_id') :: UUID AS DIA_ID,
                (C ->> 'book_on') ::TIME AS BOOK_ON,
                (C ->> 'turn_no') ::VARCHAR(20) AS TURN_NO,
                (C ->> 'Turn_text') ::VARCHAR(20) AS TURN_TEXT,
                (C ->> 'book_off') :: TIME AS BOOK_OFF,
                (C ->> 'duration') ::interval AS DURATION
            FROM FINAL,
                JSON_ARRAY_ELEMENTS((COL1)) C),
        T1 AS
        (SELECT ROW_NUMBER() OVER (ORDER BY F.DATE,F.ROWNUMBER)AS R_NO,
                F.DEPOT,
                F.GRADE,
                F.VALID_FROM,
                F.VALID_TO,
                F.ROWNUMBER,
                F.COL,
                F.COL1,
                F.DATE,
                F.DAY,
                T.DIA_ID,
                T.BOOK_ON,
                T.TURN_NO,
                T.TURN_TEXT,
                T.BOOK_OFF,
                T.DURATION
            FROM TT1 T
            FULL JOIN FINAL F ON T.ROWNUMBER = F.ROWNUMBER
            AND T.DATE = F.DATE
            AND T.COL = F.COL),
        T2 AS
        (SELECT *,
                GENERATE_SERIES(WEEKSTARTDATE,

                    WEEKENDDATE, interval '1 day')::date AS D_DATE
            FROM ROSTER_TABLE
            ORDER BY D_DATE,
                LINE_POSITION)
    INSERT INTO CREW_ROSTER.PUBLISH_ROSTER (PAYNUMBER,DEPOT,GRADE,R_ID,ROSTER_DATE,DAY,TURNNO,TURNNO_TEXT,BOOK_ON,BOOK_OFF,DURATION,DIAGRAM_ID,INSERTION_TIME)
    SELECT PAYNUMBER,DEPOT,GRADE,ROSTER_ID, date, DAY,TURN_NO,  TURN_TEXT,  BOOK_ON,    BOOK_OFF,   DURATION,   DIA_ID,NOW()
    FROM T1
    INNER JOIN T2 ON T2.D_DATE = T1.DATE
    AND T2.LINE_POSITION = T1.ROWNUMBER
    ORDER BY D_DATE,
        LINE_POSITION ASC;
END;
$BODY$;

方案2：用递归CTE替代循环，保持SQL函数类型 #

如果不想改用PL/pgSQL，可以把DO块里的循环逻辑转换成递归CTE，这样整个函数都是纯SQL语句，避免独立执行块的作用域问题。不过这种方式对复杂循环的适配性较差，仅适合逻辑简单的场景。

方案3：使用会话级临时视图替代临时表 #

临时视图和临时表一样是会话级的，但在SQL函数中，视图的定义可以被后续语句识别（只要定义在前、引用在后），不过对于需要多次插入更新的场景，视图灵活性不如临时表。

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