Temporary table does not seem to get created inside SQL function?

Question

I have created below function successfully, but get an error while executing it:

temp table doesn't exist while executing the function

CREATE OR REPLACE FUNCTION api."post_publish_Roster"()
    RETURNS void
    LANGUAGE 'sql'
AS $BODY$

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 $$
DECLARE
   weekstart INTEGER;
   weekend INTEGER ;
BEGIN
   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;
END $$;

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

But when I execute the same, from drop temp table to end insert select *, it works fine outside the function.

Why is it not possible to create the temp table inside the function?
What is alternative?

Answer 1

Like Tom Lane already suggested in pgsql-general, you cannot use any objects in a plain SQL function that have been created in the same function. That's because the whole function body is parsed before any of it is executed. Later code in the same function body cannot see objects, yet, that are created earlier in the same function.

See:

• Why can PL/pgSQL functions have side effect, while SQL functions can't?
• Difference between language sql and language plpgsql in PostgreSQL functions

Use PL/pgSQL instead. Since you are not returning anything, you might as well make it a PROCEDURE. See:

• In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

On top of that, your code can be improved in many ways. I started to clean up, but stopped at this:

CREATE OR REPLACE PROCEDURE api."post_publish_Roster"()  -- better avoid use CaMeL-case names!
  LANGUAGE plpgsql AS                                             -- !!!
$proc$
DECLARE
   _weekstart integer;                                            -- distinguish variables from column names! ②
   _weekend   integer;
BEGIN
   SELECT min(weeknumber), max(weeknumber)  INTO _weekstart, _weekend
   FROM   get_weeks('2023-02-12', '2023-03-04');                  -- definition of get_weeks() missing!

   DROP TABLE IF EXISTS pg_temp.roster_table;                     -- schemna-qualify to be safe ③

   CREATE TEMP TABLE roster_table AS
   SELECT l.roster_id, l.link_id, l.paynumber, l.username, l.line_position, l.crewname
        , w.weeknumber, w.weekstartdate, w.weekenddate            -- had to guess table-qualifications
   FROM   crew_links.links_map l
   CROSS  JOIN LATERAL (
      SELECT *
      FROM   get_weeks('2023-02-12','2023-03-04') weekdata
      WHERE  weekdata.weeknumber = _weekstart                     -- much more efficient equivalent!
      ) w
   WHERE  l.roster_id = 234;

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

   WITH div AS (  -- much simpler & cheaper
      SELECT r.depot, r.grade, r.valid_from, r.valid_to, rd.rownumber, v.*
      FROM   crew_roster.roster_name r
      JOIN   crew_roster.draft rd USING (r_id)
      CROSS  JOIN LATERAL (
         VALUES                                                      -- see ④
           ('sunday'  , rd.sunday::json)
         , ('monday'  , rd.monday::json)
         -- ... fill in
         , ('saturday', rd.saturday::json)
         ) v(col, col1)
      WHERE  r.r_id = 234
      )
   , day AS (
      SELECT date::date, to_char(date, 'FMday') AS day               -- see ⑤
      FROM   generate_series(timestamp '2023-02-12'                  -- see ⑥
                           , timestamp '2023-03-04'
                           , interval '1 day') date
      )
   , final AS (
      SELECT *
      FROM   div c
      JOIN   day d ON d.day = c.col
      ORDER  BY date, rownumber
      )
   , 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 USING (rownumber, date, 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
   JOIN   t2 ON t2.d_date = t1.date
            AND t2.line_position = t1.rownumber
   ORDER  BY d_date, line_position;
END
$proc$;

① Are PostgreSQL column names case-sensitive?

② "missing FROM-clause entry" in PLPGSQL function to update a table

③ How does the search_path influence identifier resolution and the "current schema"

④ Postgres: convert single row to multiple rows (unpivot)

⑤ Extract date from datetime and calculate total minutes from duration

⑥ Generating time series between two dates in PostgreSQL

Much better already, but probably can be simplified further - so that you don't even need a temporary table to begin with.