pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: How to store query result into another table using stored procedure
Date: Fri, 9 Jun 2023 04:01:07 -0500
Message-ID: <13e6633f-7959-1731-0165-9954b1012b80@gmail.com> (raw)
In-Reply-To: <CAJWX+ENZKWKbEC1-v4efm3AhAAY8gMjZpFqzYdemZ5K50MgpNQ@mail.gmail.com>
References: <CAJWX+ENZKWKbEC1-v4efm3AhAAY8gMjZpFqzYdemZ5K50MgpNQ@mail.gmail.com>

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.

view thread (3+ messages)  latest in thread

Message-ID: <13e6633f-7959-1731-0165-9954b1012b80@gmail.com>
Permalink:  ../13e6633f-7959-1731-0165-9954b1012b80@gmail.com/
Also on:    postgresql.org/message-id/13e6633f-7959-1731-0165-9954b1012b80@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: How to store query result into another table using stored procedure
  In-Reply-To: <13e6633f-7959-1731-0165-9954b1012b80@gmail.com>
  References: <CAJWX+ENZKWKbEC1-v4efm3AhAAY8gMjZpFqzYdemZ5K50MgpNQ@mail.gmail.com> <13e6633f-7959-1731-0165-9954b1012b80@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox