By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
-- get "variable" via CTE
create or replace function remaining_period(start date, months int)
returns interval
language sql
immutable strict
as $$
with remaining(tm) as
(select age((start + (months || ' months')::interval)::date, now()::date) as remaining_time)
select case when tm > interval '0 day'
then tm
else interval '0 day'
end
from remaining;
$$;
-- Just use calculation directly
create or replace function remaining_period2(start date, months int)
returns interval
language sql
immutable strict
as $$
select greatest( age((start + (months || ' months')::interval)::date, now()::date)
, interval '0 day'
);
$$;
CREATE FUNCTION
CREATE FUNCTION
with test_data (test_date, test_months) as
( values ('2020-01-01'::date, 6)
, ('2024-01-01'::date, 6)
, ('2025-01-01'::date, 6)
)
select test_date
, remaining_period(test_date, test_months)
, remaining_period2(test_date, test_months)
from test_data;
| test_date | remaining_period | remaining_period2 |
|---|---|---|
| 2020-01-01 | 00:00:00 | 00:00:00 |
| 2024-01-01 | 4 mons 22 days | 4 mons 22 days |
| 2025-01-01 | 1 year 4 mons 22 days | 1 year 4 mons 22 days |
SELECT 3