pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedTempory table is not getting created inside Function in postgres.
7+ messages / 4 participants
[nested] [flat]
* Tempory table is not getting created inside Function in postgres.
@ 2023-02-27 19:10 nikhil raj <nikhilraj474@gmail.com>
2023-02-27 19:47 ` Re: Tempory table is not getting created inside Function in postgres. Adrian Klaver <adrian.klaver@aklaver.com>
2023-02-27 20:04 ` Re: Tempory table is not getting created inside Function in postgres. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: nikhil raj @ 2023-02-27 19:10 UTC (permalink / raw)
To: pgsql-general
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]
Attachments:
pgAdmin.png (image/png, 48.6K) download
view image
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Tempory table is not getting created inside Function in postgres.
2023-02-27 19:10 Tempory table is not getting created inside Function in postgres. nikhil raj <nikhilraj474@gmail.com>
@ 2023-02-27 19:47 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-02-27 20:01 ` Re: Tempory table is not getting created inside Function in postgres. Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2023-02-27 19:47 UTC (permalink / raw)
To: nikhil raj <nikhilraj474@gmail.com>; pgsql-general
On 2/27/23 11:10 AM, nikhil raj wrote:
> 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
You are running this in pgAdmin4 Query Tool, correct?
Is Autocommit set?
Have you tried this in psql?
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Tempory table is not getting created inside Function in postgres.
2023-02-27 19:10 Tempory table is not getting created inside Function in postgres. nikhil raj <nikhilraj474@gmail.com>
2023-02-27 19:47 ` Re: Tempory table is not getting created inside Function in postgres. Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-02-27 20:01 ` Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 7+ messages in thread
From: Adrian Klaver @ 2023-02-27 20:01 UTC (permalink / raw)
To: nikhil raj <nikhilraj474@gmail.com>; +Cc: pgsql-general
On 2/27/23 11:52 AM, nikhil raj wrote:
Reply to list also
Ccing list.
> HI Adrian,
>
> Yes, I have tried it through the same error.
>
> Orion_db=> select api."post_publish_Roster"()
> Orion_db -> ;
> ERROR: relation "roster_table" does not exist
> LINE 94: ... interval '1 day')::date as d_date FROM roster_tab...
> ^
> QUERY:
>
> 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
I missed it the first time, you are embedding a DO inside the function.
1) Pretty sure that is not going to work. Especially as you are changing
languages.
2) Why are you doing that?
3) Either incorporate everything into one function or create separate
stand alone function for the DO portion and use that in the
post_publish_Roster
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Tempory table is not getting created inside Function in postgres.
2023-02-27 19:10 Tempory table is not getting created inside Function in postgres. nikhil raj <nikhilraj474@gmail.com>
@ 2023-02-27 20:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-27 20:17 ` Re: Tempory table is not getting created inside Function in postgres. nikhil raj <nikhilraj474@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2023-02-27 20:04 UTC (permalink / raw)
To: nikhil raj <nikhilraj474@gmail.com>; +Cc: pgsql-general
nikhil raj <nikhilraj474@gmail.com> writes:
> This is the Function I have created successfully but while executing it
> throughs an error temp table doesn't exist.
You won't be able to do it like that in a SQL-language function, because
the whole function body is parsed and parse-analyzed in one go. So the
later query referencing ROSTER_TABLE fails because it's parsed before
the CREATE TABLE executes. (Improving that has been on the to-do list
for a couple of decades, so don't hold your breath...) I suggest putting
the whole thing, not just part of it, into plpgsql.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Tempory table is not getting created inside Function in postgres.
2023-02-27 19:10 Tempory table is not getting created inside Function in postgres. nikhil raj <nikhilraj474@gmail.com>
2023-02-27 20:04 ` Re: Tempory table is not getting created inside Function in postgres. Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-02-27 20:17 ` nikhil raj <nikhilraj474@gmail.com>
2023-02-27 20:40 ` Re: Tempory table is not getting created inside Function in postgres. David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: nikhil raj @ 2023-02-27 20:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general
Hi Tom,
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.
On Tue, 28 Feb, 2023, 1:34 am Tom Lane, <tgl@sss.pgh.pa.us> wrote:
> nikhil raj <nikhilraj474@gmail.com> writes:
> > This is the Function I have created successfully but while executing it
> > throughs an error temp table doesn't exist.
>
> You won't be able to do it like that in a SQL-language function, because
> the whole function body is parsed and parse-analyzed in one go. So the
> later query referencing ROSTER_TABLE fails because it's parsed before
> the CREATE TABLE executes. (Improving that has been on the to-do list
> for a couple of decades, so don't hold your breath...) I suggest putting
> the whole thing, not just part of it, into plpgsql.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Tempory table is not getting created inside Function in postgres.
2023-02-27 19:10 Tempory table is not getting created inside Function in postgres. nikhil raj <nikhilraj474@gmail.com>
2023-02-27 20:04 ` Re: Tempory table is not getting created inside Function in postgres. Tom Lane <tgl@sss.pgh.pa.us>
2023-02-27 20:17 ` Re: Tempory table is not getting created inside Function in postgres. nikhil raj <nikhilraj474@gmail.com>
@ 2023-02-27 20:40 ` David G. Johnston <david.g.johnston@gmail.com>
2023-02-28 09:54 ` Re: Tempory table is not getting created inside Function in postgres. nikhil raj <nikhilraj474@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2023-02-27 20:40 UTC (permalink / raw)
To: nikhil raj <nikhilraj474@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general
On Mon, Feb 27, 2023 at 1:17 PM nikhil raj <nikhilraj474@gmail.com> wrote:
>
> 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.
>
>>
>>
What Tom said is that in the following change 'sql' to plpgsql and move
on. Your function is not capable of being executed in an SQL language
function.
CREATE OR REPLACE FUNCTION api."post_publish_Roster"(
)
RETURNS void
LANGUAGE 'sql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Tempory table is not getting created inside Function in postgres.
2023-02-27 19:10 Tempory table is not getting created inside Function in postgres. nikhil raj <nikhilraj474@gmail.com>
2023-02-27 20:04 ` Re: Tempory table is not getting created inside Function in postgres. Tom Lane <tgl@sss.pgh.pa.us>
2023-02-27 20:17 ` Re: Tempory table is not getting created inside Function in postgres. nikhil raj <nikhilraj474@gmail.com>
2023-02-27 20:40 ` Re: Tempory table is not getting created inside Function in postgres. David G. Johnston <david.g.johnston@gmail.com>
@ 2023-02-28 09:54 ` nikhil raj <nikhilraj474@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: nikhil raj @ 2023-02-28 09:54 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general
HI All,
This for the help plpgsql and rest everything just move under declare and
now everything works fine. Thanks for the help.
On Tue, Feb 28, 2023 at 2:10 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Mon, Feb 27, 2023 at 1:17 PM nikhil raj <nikhilraj474@gmail.com> wrote:
>
>>
>> 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.
>>
>>>
>>>
> What Tom said is that in the following change 'sql' to plpgsql and move
> on. Your function is not capable of being executed in an SQL language
> function.
>
> CREATE OR REPLACE FUNCTION api."post_publish_Roster"(
> )
> RETURNS void
> LANGUAGE 'sql'
> COST 100
> VOLATILE PARALLEL UNSAFE
> AS $BODY$
>
> David J.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-02-28 09:54 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-27 19:10 Tempory table is not getting created inside Function in postgres. nikhil raj <nikhilraj474@gmail.com>
2023-02-27 19:47 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-02-27 20:01 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-02-27 20:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-27 20:17 ` nikhil raj <nikhilraj474@gmail.com>
2023-02-27 20:40 ` David G. Johnston <david.g.johnston@gmail.com>
2023-02-28 09:54 ` nikhil raj <nikhilraj474@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox