Postgres 14

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?.

-- extended test case
CREATE TABLE example(row_num int, id int, str text, val integer);
INSERT INTO example VALUES
  (1, 1, '1a', NULL)
, (2, 1, NULL,    1)
, (3, 2, '2a',    2)
, (4, 2, NULL, NULL)
, (5, 3, NULL, NULL)
, (6, 3, '3a',   31)
, (7, 3, NULL, NULL)
, (8, 3, NULL,   32)
, (9, 3, '3b', NULL)
, (10,3, NULL, NULL)
;

CREATE TABLE



INSERT 0 10


-- your plpgsql function
CREATE OR REPLACE FUNCTION GapFillInternal(s anyelement, v anyelement)
  RETURNS anyelement
  LANGUAGE plpgsql AS 
$$
DECLARE
    TEMP ALIAS FOR $0 ;
BEGIN
    RAISE NOTICE 's= %, v= %', s, v;
    IF v IS NULL AND s NOTNULL THEN
        TEMP := s;
    ELSIF s IS NULL AND v NOTNULL THEN
        TEMP := v;
    ELSIF s NOTNULL AND v NOTNULL THEN
        TEMP := v;
    ELSE
        TEMP := NULL;
    END IF;
    RAISE NOTICE 'temp= %', TEMP;
    RETURN TEMP;
END
$$;

CREATE AGGREGATE GapFill(anyelement) ( 
  SFUNC = GapFillInternal, 
  STYPE = anyelement 
);

CREATE FUNCTION



CREATE AGGREGATE


-- your call (problem in row 5!)
select row_num, id
     , str, GapFill(str) OVER (order by row_num) as strx
     , val, GapFill(val) OVER (order by row_num) as valx
from example;


row_num
id
str
strx
val
valx


1
1
1a
1a
null
null

2
1
null
1a
1
1

3
2
2a
2a
2
2

4
2
null
2a
null
2

5
3
null
2a
null
2

6
3
3a
3a
31
31

7
3
null
3a
null
31

8
3
null
3a
32
32

9
3
3b
3b
null
32

10
3
null
3b
null
32



SELECT 10


-- correct call (?)
SELECT row_num, id
     , str, gapfill(str) OVER w AS strx
     , val, gapfill(val) OVER w AS valx
FROM   example
WINDOW w AS (PARTITION BY id ORDER BY row_num);


row_num
id
str
strx
val
valx


1
1
1a
1a
null
null

2
1
null
1a
1
1

3
2
2a
2a
2
2

4
2
null
2a
null
2

5
3
null
null
null
null

6
3
3a
3a
31
31

7
3
null
3a
null
31

8
3
null
3a
32
32

9
3
3b
3b
null
32

10
3
null
3b
null
32



SELECT 10


-- better aggregate function
CREATE OR REPLACE FUNCTION gap_fill_internal(s anyelement, v anyelement)
  RETURNS anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
RETURN COALESCE(v, s);  -- that's all!
END
$func$;

CREATE AGGREGATE gap_fill(anyelement) ( 
  SFUNC = gap_fill_internal, 
  STYPE = anyelement 
);

CREATE FUNCTION



CREATE AGGREGATE


-- correct call with better function
SELECT row_num, id
     , str, gap_fill(str) OVER w AS strx
     , val, gap_fill(val) OVER w AS valx
FROM   example
WINDOW w AS (PARTITION BY id ORDER BY row_num);


row_num
id
str
strx
val
valx


1
1
1a
1a
null
null

2
1
null
1a
1
1

3
2
2a
2a
2
2

4
2
null
2a
null
2

5
3
null
null
null
null

6
3
3a
3a
31
31

7
3
null
3a
null
31

8
3
null
3a
32
32

9
3
3b
3b
null
32

10
3
null
3b
null
32



SELECT 10


-- standard SQL without custom function
SELECT row_num, id
     , str, first_value(str) OVER (PARTITION BY id, ct_str ORDER BY row_num) AS strx
     , val, first_value(val) OVER (PARTITION BY id, ct_val ORDER BY row_num) AS valx
FROM (     
   SELECT *, count(str) OVER w AS ct_str, count(val) OVER w AS ct_val
   FROM   example
   WINDOW w AS (PARTITION BY id ORDER BY row_num)
   ) sub;


row_num
id
str
strx
val
valx


1
1
1a
1a
null
null

2
1
null
1a
1
1

3
2
2a
2a
2
2

4
2
null
2a
null
2

5
3
null
null
null
null

6
3
3a
3a
31
31

7
3
null
3a
null
31

8
3
null
3a
32
32

9
3
3b
3b
null
32

10
3
null
3b
null
32



SELECT 10

row_num	id	str	strx	val	valx
1	1	1a	1a	null	null
2	1	null	1a	1	1
3	2	2a	2a	2	2
4	2	null	2a	null	2
5	3	null	2a	null	2
6	3	3a	3a	31	31
7	3	null	3a	null	31
8	3	null	3a	32	32
9	3	3b	3b	null	32
10	3	null	3b	null	32