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....
[image: pgAdmin.png]
