pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Tempory 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