pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Erik Wienhold <ewie@ewie.name>
To: Rama Krishnan <raghuldrag@gmail.com>
To: Postgres General <pgsql-general@postgresql.org>
Subject: Re: How to store query result into another table using stored procedure
Date: Fri, 9 Jun 2023 12:46:37 +0200 (CEST)
Message-ID: <1833100773.179651.1686307597426@office.mailbox.org> (raw)
In-Reply-To: <CAJWX+ENZKWKbEC1-v4efm3AhAAY8gMjZpFqzYdemZ5K50MgpNQ@mail.gmail.com>
References: <CAJWX+ENZKWKbEC1-v4efm3AhAAY8gMjZpFqzYdemZ5K50MgpNQ@mail.gmail.com>

> On 09/06/2023 07:51 CEST Rama Krishnan <raghuldrag@gmail.com> 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





view thread (3+ messages)

Message-ID: <1833100773.179651.1686307597426@office.mailbox.org>
Permalink:  ../1833100773.179651.1686307597426@office.mailbox.org/
Also on:    postgresql.org/message-id/1833100773.179651.1686307597426@office.mailbox.org

 · 

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: ewie@ewie.name, raghuldrag@gmail.com
  Subject: Re: How to store query result into another table using stored procedure
  In-Reply-To: <1833100773.179651.1686307597426@office.mailbox.org>
  References: <CAJWX+ENZKWKbEC1-v4efm3AhAAY8gMjZpFqzYdemZ5K50MgpNQ@mail.gmail.com> <1833100773.179651.1686307597426@office.mailbox.org>

* 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