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?.
create table your_table
(code numeric (5,2),
versioned character(1),
year integer);
insert into your_table values
(10, 'A', 2000),
(11, 'B', 2001),
(12, 'C', 2002),
(13, 'A', 1999),
(15, 'D', 1998);
5 rows affected
CREATE OR REPLACE FUNCTION totalRecords1()
RETURNS integer AS $total1$
declare
total1 integer;
BEGIN
SELECT count(*) into total1
FROM your_table
WHERE year >= 2000;
RETURN total1;
END;
$total1$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION totalRecords2()
RETURNS integer AS $total2$
declare
total2 integer;
BEGIN
SELECT count(*) into total2
FROM your_table
WHERE year < 2000;
RETURN total2;
END;
$total2$ LANGUAGE plpgsql;
select totalrecords1(), totalrecords2();
| totalrecords1 | totalrecords2 |
|---|---|
| 3 | 2 |