How to store query result into another table using stored procedure

From: Rama Krishnan <raghuldrag(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: How to store query result into another table using stored procedure
Date: 2023-06-09 05:51:20
Message-ID: CAJWX+ENZKWKbEC1-v4efm3AhAAY8gMjZpFqzYdemZ5K50MgpNQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Regards

A.Rama Krishnan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alex Lee 2023-06-09 06:54:42 How to securely isolate databases/users in a multi-tenant Postgresql?
Previous Message Amit Kapila 2023-06-09 05:38:42 Re: Support logical replication of DDLs