postgresql while loop with declared vars

Question

I have from outside timestamp range for example from '2022-04-01' to '2022-04-07' and i need go through each date from this range and execute SELECT query and expect to receive 7 rows in result. I tried next construction:

DO $$DECLARE 
_first_date timestamp := '2022-04-01';
_second_date timestamp := '2022-04-07';
BEGIN
WHILE _first_date<=_second_date LOOP
SELECT :_first_date, count(*) from my_table where date_column_in_my_table = :_first_date;
_first_date := _first_date + interval '1' day;
END LOOP;
END$$;

but i have syntax error at or near ":" on ':_first_date' line. Next i tried another test query:

DO $$DECLARE 
_first_date timestamp := '2022-04-01';
_second_date timestamp := '2022-04-07';
BEGIN
WHILE _first_date<=_second_date LOOP
select 'test_text';
_first_date := _first_date + interval '1' day;
END LOOP;
END$$;

but now i have: 'ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement', which seems to say that SELECT query does not work with this conditions.

Can I somehow get the desired result in the result set:

2022-04-01, 5
2022-04-02, 12
2022-04-03, 6
2022-04-04, 3
2022-04-05, 4
2022-04-06, 11
2022-04-07, 0

?

@a_horse_with_no_name How can i do this?

Andrey Pylyptiy
– Andrey Pylyptiy

2022-04-07 08:51:17 +00:00
Commented Apr 7, 2022 at 8:51 — Andrey Pylyptiy
– Andrey Pylyptiy, Commented Apr 7, 2022 at 8:51

user330315 · Accepted Answer · 2022-04-07 08:51:06Z

1

A DO block can't return a result. But there is no need to use a loop or PL/pgSQL for this. You can use generate_series() to generate the list of dates and then join that to your table. If you make that an outer join, you also get a zero count for dates that don't exist in your table:

select g.dt::date, count(t.date_column_in_my_table) 
from generate_series(date '2022-04-01', 
                     date '2022-04-07', 
                     interval '1 day') as g(dt)
  left join my_table t on t.date_column_in_my_table := g.dt::date
group by g.dt::date

The cast g.dt::date is necessary because generate_series() always returns a timestamp, even if the parameters are all dates.

answered Apr 7, 2022 at 8:51

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql while loop with declared vars

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related