Re: How to store query result into another table using stored procedure

[Ron <ronljohnsonjr@xxxxxxxxx>]

    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.