Thread: Tempory table is not getting created inside Function in postgres.

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....

The same query  is executed outside the function its working properly  means just the qurey from drop temp table to insert but when i keep the query inside a function its through me the error an after temp ra how creation only i am using the select statment still its not exectuing the create  statment or not storing the temp table.

This for the help  plpgsql  and rest everything just move under declare and now everything works fine.  Thanks for the help.