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 1q7Zdn-00067t-CG for pgsql-general@arkaria.postgresql.org; Fri, 09 Jun 2023 10:46:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q7Zdl-0007OY-W2 for pgsql-general@arkaria.postgresql.org; Fri, 09 Jun 2023 10:46:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q7Zdl-0007Ns-L0 for pgsql-general@lists.postgresql.org; Fri, 09 Jun 2023 10:46:45 +0000 Received: from mout-u-107.mailbox.org ([2001:67c:2050:101:465::107]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q7Zdi-001Cb4-Ow for pgsql-general@postgresql.org; Fri, 09 Jun 2023 10:46:45 +0000 Received: from smtp2.mailbox.org (smtp2.mailbox.org [10.196.197.2]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-107.mailbox.org (Postfix) with ESMTPS id 4QcySF6Rfyz9sQH; Fri, 9 Jun 2023 12:46:37 +0200 (CEST) Date: Fri, 9 Jun 2023 12:46:37 +0200 (CEST) From: Erik Wienhold To: Rama Krishnan , Postgres General Message-ID: <1833100773.179651.1686307597426@office.mailbox.org> In-Reply-To: References: Subject: Re: How to store query result into another table using stored procedure MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 09/06/2023 07:51 CEST Rama Krishnan wrote: > > CREATE or REPLACE PROCEDURE deleted_cards_count_test(start_date TIMESTAMP, end_date TIMESTAMP) AS $$ > DECLARE > current_date TIMESTAMP; > month_start_date TIMESTAMP; > month_end_date TIMESTAMP; > month24_end_date TIMESTAMP; > no_deleted_cards bigint; > BEGIN > current_date := start_date; > month_end_date := to_char(date_trunc('month', current_date) + interval '24 month - 1 day' + interval '23 hours 59 minutes 5 seconds','YYYY-MM-DD HH24:MI:SS'); > Create temporary table if not exists temp_teport_results( > month_start_date TIMESTAMP, > no_deleted_cards bigint > ); > > EXECUTE format(' > SELECT COUNT(1) filter (where status =''Undigitized'' and reason is null and updated_date between %L and %L) no_deleted_cards from digi_card where created_date between %L and %L > group by months',current_date,month_end_date)INTO no_deleted_cards; > > Insert into temp_teport_results (month_start_date,no_deleted_cards) VALUES (month_start_date,no_deleted_cards); > --- display result > select * from temp_teport_results; > END; > $$ LANGUAGE plpgsql; > > It was created successfully, but when I called this procedure with parameters. > i am getting this below error ,Pls guide me to fix the issue > > CALL deleted_cards_count_test( '2019-03-01 00:00:00', '2021-03-31 23:59:59'); > ERROR: too few arguments for format() > CONTEXT: PL/pgSQL function deleted_cards_count_test(timestamp without time zone,timestamp without time zone) line 16 at EXECUTE The problem is that you expect four arguments in format to fill the four %L. You can reuse the two arguments by using %1$L and %2$L for the third and fourth occurence of %L. But I don't think you need EXECUTE format() at all. You can instead write an INSERT SELECT statement and use the plpgsql variables in place of the format placeholders %L: INSERT INTO temp_teport_results (month_start_date, no_deleted_cards) SELECT count(1) FILTER ( WHERE status = 'Undigitized' AND reason IS NULL AND updated_date BETWEEN current_date AND month_end_date -- uses the variables ) ... You may want to prefix the variable names with v_ to easily spot them and reduce the likelyhood of conflicts with column names. Otherwise qualify the variable names with the procedure name to avoid conflicts. See the docs on variable substitution: https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST -- Erik