pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: nikhil raj <nikhilraj474@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Tempory table is not getting created inside Function in postgres.
Date: Mon, 27 Feb 2023 12:01:15 -0800
Message-ID: <94be2c64-8392-e804-28ce-ab3e16dce4d1@aklaver.com> (raw)
In-Reply-To: <CAG1ps1zLVi722O2+CPA1kOMw_d7Ss7VrRgE6uFn_zKvRWVKLNw@mail.gmail.com>
References: <CAG1ps1xOa8W3Q2T=6Tskrq2s0jPdAsaJv1-efo53Je5fvAuh0A@mail.gmail.com>
	<8abddd95-02e3-82f1-0003-ec8636d95630@aklaver.com>
	<CAG1ps1zLVi722O2+CPA1kOMw_d7Ss7VrRgE6uFn_zKvRWVKLNw@mail.gmail.com>

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





view thread (7+ messages)  latest in thread

Message-ID: <94be2c64-8392-e804-28ce-ab3e16dce4d1@aklaver.com>
Permalink:  ../94be2c64-8392-e804-28ce-ab3e16dce4d1@aklaver.com/
Also on:    postgresql.org/message-id/94be2c64-8392-e804-28ce-ab3e16dce4d1@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, nikhilraj474@gmail.com
  Subject: Re: Tempory table is not getting created inside Function in postgres.
  In-Reply-To: <94be2c64-8392-e804-28ce-ab3e16dce4d1@aklaver.com>
  References: <CAG1ps1xOa8W3Q2T=6Tskrq2s0jPdAsaJv1-efo53Je5fvAuh0A@mail.gmail.com> <8abddd95-02e3-82f1-0003-ec8636d95630@aklaver.com> <CAG1ps1zLVi722O2+CPA1kOMw_d7Ss7VrRgE6uFn_zKvRWVKLNw@mail.gmail.com> <94be2c64-8392-e804-28ce-ab3e16dce4d1@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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