Tempory table is not getting created inside Function in postgres.

[nikhil raj <nikhilraj474@xxxxxxxxx>]

HI Team,

This is the Function I have created successfully but while executing it throughs an error temp table doesn't exist.

But the same when I execute it not inside the function from **drop temp table to end insert select ** it work fine

Please can any one help me why in the function i am not able to create the temp table. what is alternative

  

    `-- FUNCTION: api.post_publish_Roster()
   
    -- DROP FUNCTION IF EXISTS api."post_publish_Roster"();
   
    CREATE OR REPLACE FUNCTION api."post_publish_Roster"(
    )
        RETURNS void
        LANGUAGE 'sql'
        COST 100
        VOLATILE PARALLEL UNSAFE
    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$;
   
    ALTER FUNCTION api."post_publish_Roster"()
        OWNER TO postgres;
   
    GRANT EXECUTE ON FUNCTION api."post_publish_Roster"() TO PUBLIC;
   
    GRANT EXECUTE ON FUNCTION api."post_publish_Roster"() TO postgres;
   
    GRANT EXECUTE ON FUNCTION api."post_publish_Roster"() TO readonlyrole;
   
    GRANT EXECUTE ON FUNCTION api."post_publish_Roster"() TO readwriterole;
   
    `
It throws this error....