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 1q7Xzf-0000ca-Kh for pgsql-general@arkaria.postgresql.org; Fri, 09 Jun 2023 09:01:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q7Xzd-0003iX-Uh for pgsql-general@arkaria.postgresql.org; Fri, 09 Jun 2023 09:01:13 +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 1q7Xzd-0003iO-H1 for pgsql-general@lists.postgresql.org; Fri, 09 Jun 2023 09:01:13 +0000 Received: from mail-yb1-xb29.google.com ([2607:f8b0:4864:20::b29]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q7Xza-001BWM-1L for pgsql-general@lists.postgresql.org; Fri, 09 Jun 2023 09:01:12 +0000 Received: by mail-yb1-xb29.google.com with SMTP id 3f1490d57ef6-bb1f7c5495dso1590449276.3 for ; Fri, 09 Jun 2023 02:01:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1686301268; x=1688893268; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=ytAz6dSbJoEOtrI1COcZOEcEmkOo47kGbhN0fEBjsLU=; b=V9Ecz8mE8+KWrMwkGxQ/nRSXbcD8TnUZu6ask9KiTcX9QkOc8Ysx997JixJdCkCtjq fVHSwSAPkznDFD2KrQa9ax3NmFYkFiVFCFXLAZOocLnJVLPMsdfAzqNVqWumcMRdt6pn 3uGZs2bZzRnzhu0bqA/cMf4QgRBTwikZ5YG6WbZrkktESdGDx5KUwv2CkEbLQaCRz/kh zFn0oVIvpdXk/6Hm5k6TcWP6DWYXacMEBNSbMmUdO2V63kP5PDbhYebaiC8O8l48d+XI 1MvGfTyoFnCmCQ+YyJPvyOenhh1WHGIMHewv03gahfg9lB2Wl2pID0UGoOp+JWJhl6eL apFg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1686301268; x=1688893268; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=ytAz6dSbJoEOtrI1COcZOEcEmkOo47kGbhN0fEBjsLU=; b=jVmRniYzDqIg21dmBb8kjb4/7nAbrGxTd3KcJJX5ddTTogue4MQGUKPLr3hU+s8pdT UYWypwHm/Nq7nXPTrRbOHFzs4oT9tvTgjrjSd8BFmiAaK63Id2D/lDowNbn6TSl2xBg/ 0zgVX36cRUjXaw4+z12kVCNvQARVLE3vYEDOS3csNpH4VeRn1ayh91IWsXuqGQAe4Av+ EvNCYOg9cTmqMudcsZCpJ0meqf/Tp3j4wlwO8c1lF7e18dIm81bJpeoVf9p3NqsFYmmr gdjTqVT10Dox7VhDPveVBchCc31kpMy8MvzhnoYArczAcfS6CYCvoyfiPuHuNiU34Edi +rkA== X-Gm-Message-State: AC+VfDx9JAyo1igIOPNaaD7WqbwLH6mRVVY+TgrxeuskeL/66MOTil7Y JSZNegjah5zxCYKpk5VCe7MU6mDlrzQ= X-Google-Smtp-Source: ACHHUZ6E2kLp+1PNFmSBLpq9lAhOL8+LjITTM53SpVrknFzDq+2NiLK13Oi3e4UgptEKy7BOvMVpEw== X-Received: by 2002:a25:8212:0:b0:bb3:8b57:8e75 with SMTP id q18-20020a258212000000b00bb38b578e75mr537019ybk.45.1686301268094; Fri, 09 Jun 2023 02:01:08 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:1e9:176a:1410:3b06? ([2600:1700:bd2c:b810:1e9:176a:1410:3b06]) by smtp.googlemail.com with ESMTPSA id g5-20020a05690203c500b00b9eeffa1abfsm766418ybs.39.2023.06.09.02.01.07 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 09 Jun 2023 02:01:07 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------dDzweh1Dzankz8GY7iDxEhaU" Message-ID: <13e6633f-7959-1731-0165-9954b1012b80@gmail.com> Date: Fri, 9 Jun 2023 04:01:07 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: How to store query result into another table using stored procedure Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------dDzweh1Dzankz8GY7iDxEhaU Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 6/9/23 00:51, Rama Krishnan wrote: > Hi All, > > > I have a table like below > > Create table if not exists digi_card( >      Digi_card_id varchar(100), >     created_date timestamp, >     updated_date timestamp, >      status varchar(50), >      reason varchar(50) > ); > > Sample values: > > Insert into digi_card values ('ee4422', '2019-03-01 00:25:00', '2021-03-31 > 22:33:00','Active','NULL'); > Insert into digi_card values ('ee4423', '2019-08-01 00:25:00', '2022-07-31 > 00:33:00','Undigiized ','Move'); > Insert into digi_card values ('ee4424', '2021-03-01 00:25:00', '2023-02-27 > 08:33:00','Active','NULL'); > > > I want to display the card which was deleted after 24 months from the > corresponding  created month and the results should be store on the > temporary tables so i have written the below stored procedure > > 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 Does the format() work outside of the stored procedure?  In psql, for example: SELECT 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'                     ,'2023-06-08','2023-06-30'); I tried it, and it doesn't: ostgres=# SELECT format('SELECT COUNT(1) filter (where status =''Undigitized'' postgres'#                             and reason is null and updated_date between %L and %L) no_deleted_cards postgres'#                      from digi_card where created_date between %L and %L group by months' postgres(#                     ,'2023-06-08','2023-06-30'); ERROR:  too few arguments for format() Because you've got four arguments, and you were only passing two. This works: postgres=# SELECT 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'                     ,'2023-06-08','2023-06-30', '2023-06-08','2023-06-30'); format -------------------------------------------------------------------------------------------------------------------------  SELECT COUNT(1) filter (where status ='Undigitized' +                              and reason is null and updated_date between '2023-06-08' and '2023-06-30') no_deleted_cards+                       from digi_card where created_date between '2023-06-08' and '2023-06-30' group by months (1 row) -- Born in Arizona, moved to Babylonia. --------------dDzweh1Dzankz8GY7iDxEhaU Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 6/9/23 00:51, Rama Krishnan wrote:
Hi All,


I have a table like below

Create table if not exists digi_card(
     Digi_card_id varchar(100),
    created_date timestamp,
    updated_date timestamp,
     status varchar(50),
     reason varchar(50)
);

Sample values:

Insert into digi_card values ('ee4422', '2019-03-01 00:25:00', '2021-03-31 22:33:00','Active','NULL');
Insert into digi_card values ('ee4423', '2019-08-01 00:25:00', '2022-07-31 00:33:00','Undigiized ','Move');
Insert into digi_card values ('ee4424', '2021-03-01 00:25:00', '2023-02-27 08:33:00','Active','NULL');


I want to display the card which was deleted after 24 months from the corresponding  created month and the results should be store on the temporary tables so i have written the below stored procedure 

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

Does the format() work outside of the stored procedure?  In psql, for example:
SELECT 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'
                    ,'2023-06-08','2023-06-30');

I tried it, and it doesn't:
ostgres=# SELECT format('SELECT COUNT(1) filter (where status =''Undigitized''
postgres'#                             and reason is null and updated_date between %L and %L) no_deleted_cards
postgres'#                      from digi_card where created_date between %L and %L group by months'
postgres(#                     ,'2023-06-08','2023-06-30');
ERROR:  too few arguments for format()

Because you've got four arguments, and you were only passing two.

This works:
postgres=# SELECT 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'
                    ,'2023-06-08','2023-06-30', '2023-06-08','2023-06-30');
                                                         format                                                          
-------------------------------------------------------------------------------------------------------------------------
 SELECT COUNT(1) filter (where status ='Undigitized'                                                                    +
                             and reason is null and updated_date between '2023-06-08' and '2023-06-30') no_deleted_cards+
                      from digi_card where created_date between '2023-06-08' and '2023-06-30' group by months
(1 row)


--
Born in Arizona, moved to Babylonia.
--------------dDzweh1Dzankz8GY7iDxEhaU--