Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWjgY-0004ed-Tt for pgsql-general@arkaria.postgresql.org; Mon, 27 Feb 2023 20:01:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pWjgX-00035D-L1 for pgsql-general@arkaria.postgresql.org; Mon, 27 Feb 2023 20:01:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWjgX-00034z-AY for pgsql-general@lists.postgresql.org; Mon, 27 Feb 2023 20:01:21 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWjgU-0008JL-M6 for pgsql-general@postgresql.org; Mon, 27 Feb 2023 20:01:20 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.nyi.internal (Postfix) with ESMTP id EDE315C007E; Mon, 27 Feb 2023 15:01:17 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute6.internal (MEProxy); Mon, 27 Feb 2023 15:01:17 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to; s=fm3; t=1677528077; x= 1677614477; bh=MISQJw4VGsQzR9paK9qVj9DPxZVB03yszLoptYg9po0=; b=h KfGQdFRtrzGKpXCLyf82wuKN13UqE82CVkUFapQHQfk5A4Q70zh5/lThh8Nn2RRx sg4EoEvkoPWIO5bB0+Tnf8u6agCjcb3SUmntpOlbw989YVJBHIIEfDNHsJdwXCsA x75KTbIZ0aZZ7tdiAAnfI0Xaa6CBzCO8CG0zexDhaXv1SR2O5XGYPoS0v85QsJh6 m0u0zfBdihdOXknXSk+ezwsaPzEXUnhI3JEVqC6eH6Cl/LaCOqaSaJ/UThIXwcIO G+nyW/WeqPotp2cTAr5ZE3oUpR35ipPThM/lJLi+9nAJCU+KFNVgqp6EEmPqOZZb 9JZYLB7uimw4IEYQEJa0A== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1677528077; x= 1677614477; bh=MISQJw4VGsQzR9paK9qVj9DPxZVB03yszLoptYg9po0=; b=h IdCnsqOaeG2Tih045KVoAsZJwLBxGYlGVo+sY6f2I/7qxYQFFPf51G3+K0r7Mb9I Ds/oSZ8eO9/gf7JDlH3tYajj8BJpBAJxnRuTlTyaR023rkZOWWAItR9qEGLv5uJE DeS6WzI3gm1jKyFESJObZauLRFiBCjoFRV4UhJTsERnoYFZ65ndkNj0MYcaEpgVW KxsQp/Tfw50ubmN6b1O7vGRsTlr38bSK6cllVBwv7dRk1Kr2lx8sg91UaHQ6L8Cb zrmIqrp7iBUruwmbMnZQJFIwq+qLNGwRJcmFLVagy5q4D70qgUsqz4XQME1zA+sb s1cGMNi5ROB8luUdfZ68A== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrudeltddguddvkecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enucfjughrpefuvfhfvefhkffffgggjggtgfesthekredttdefjeenucfhrhhomheptegu rhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrd gtohhmqeenucggtffrrghtthgvrhhnpefggeejkeevjeefffefkeeufffhkeetfedvleev geeuuddujeffueefueffieefkeenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmh epmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 27 Feb 2023 15:01:16 -0500 (EST) Subject: Re: Tempory table is not getting created inside Function in postgres. To: nikhil raj References: <8abddd95-02e3-82f1-0003-ec8636d95630@aklaver.com> Cc: pgsql-general From: Adrian Klaver Message-ID: <94be2c64-8392-e804-28ce-ab3e16dce4d1@aklaver.com> Date: Mon, 27 Feb 2023 12:01:15 -0800 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.7.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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