pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to store query result into another table using stored procedure
3+ messages / 3 participants
[nested] [flat]
* How to store query result into another table using stored procedure
@ 2023-06-09 05:51 Rama Krishnan <raghuldrag@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Rama Krishnan @ 2023-06-09 05:51 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to store query result into another table using stored procedure
@ 2023-06-09 09:01 Ron <ronljohnsonjr@gmail.com>
parent: Rama Krishnan <raghuldrag@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ron @ 2023-06-09 09:01 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to store query result into another table using stored procedure
@ 2023-06-09 10:46 Erik Wienhold <ewie@ewie.name>
parent: Rama Krishnan <raghuldrag@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Erik Wienhold @ 2023-06-09 10:46 UTC (permalink / raw)
To: Rama Krishnan <raghuldrag@gmail.com>; pgsql-general
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-06-09 10:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-09 05:51 How to store query result into another table using stored procedure Rama Krishnan <raghuldrag@gmail.com>
2023-06-09 09:01 ` Ron <ronljohnsonjr@gmail.com>
2023-06-09 10:46 ` Erik Wienhold <ewie@ewie.name>
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