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?.
-- test tables
CREATE TABLE empty();

CREATE TABLE all_notnull(row_num int NOT NULL, id int NOT NULL, col text NOT NULL);

CREATE TABLE example(row_num int NOT NULL, id int NOT NULL, 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



CREATE TABLE



CREATE TABLE



INSERT 0 10


-- custom aggregate
CREATE OR REPLACE FUNCTION gap_fill_internal(s anyelement, v anyelement)
  RETURNS anyelement
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE COST 1 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


-- demo to just SELECT
CREATE OR REPLACE FUNCTION f_gap_fill_select(_tbl_type anyelement, _id text, _row_num text)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tbl regclass := pg_typeof(_tbl_type)::text::regclass;
   _sql text;
BEGIN   
   SELECT INTO _sql
          'SELECT ' || string_agg(CASE WHEN a.attnotnull
                                  THEN format('%I', a.attname)
                                  ELSE format('gap_fill(%1$I) OVER w AS %1$I', a.attname) END
                                , ', ' ORDER BY a.attnum)
        || E'\nFROM ' || _tbl
        || format(E'\nWINDOW w AS (PARTITION BY %I ORDER BY %I)', _id, _row_num)
   FROM   pg_attribute a
   WHERE  a.attrelid = _tbl
   AND    a.attnum > 0
   AND    NOT a.attisdropped;
   
   IF _sql IS NULL THEN
      RAISE EXCEPTION 'SQL string is NULL. Should not occur!';
   END IF;

   RETURN QUERY EXECUTE _sql;
   -- RAISE NOTICE '%', _sql;       -- debug
END
$func$;

CREATE FUNCTION


-- NOTE the call!
SELECT * FROM f_gap_fill_select(NULL::example, 'id', 'row_num');


row_num
id
str
val


1
1
1a
null

2
1
1a
1

3
2
2a
2

4
2
2a
2

5
3
null
null

6
3
3a
31

7
3
3a
31

8
3
3a
32

9
3
3b
32

10
3
3b
32



SELECT 10


-- to UPDATE
CREATE OR REPLACE FUNCTION f_gap_fill_update(_tbl regclass, _id text, _row_num text, OUT nullable_columns int, OUT updated_rows int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _pk  text  := quote_ident(_row_num);
   _sql text;
BEGIN   
   SELECT INTO _sql, nullable_columns
          concat_ws(E'\n'
          , 'UPDATE ' || _tbl || ' t'
          , 'SET   (' || string_agg(        quote_ident(a.attname), ', ') || ')'
          , '    = (' || string_agg('u.' || quote_ident(a.attname), ', ') || ')'
          , 'FROM  (' 
          , '   SELECT ' || _pk
          , '        , ' || string_agg(format('gap_fill(%1$I) OVER w AS %1$I', a.attname), ', ')
          , '   FROM   ' || _tbl
          , format('   WINDOW w AS (PARTITION BY %I ORDER BY %s)', _id, _pk)
          , '   ) u'
          , format('WHERE t.%1$s = u.%1$s', _pk)
          , 'AND  (' || string_agg('t.' || quote_ident(a.attname), ', ') || ') IS DISTINCT FROM'
          , '     (' || string_agg('u.' || quote_ident(a.attname), ', ') || ')'
          )
        , count(*) -- AS _col_ct
   FROM  (
      SELECT a.attname
      FROM   pg_attribute a
      WHERE  a.attrelid = _tbl
      AND    a.attnum > 0
      AND    NOT a.attisdropped
      AND    NOT a.attnotnull
      ORDER  BY a.attnum
      ) a;

   IF nullable_columns = 0 THEN
      RAISE EXCEPTION 'No nullable columns found in table >>%<<', _tbl;
   ELSIF _sql IS NULL THEN
      RAISE EXCEPTION 'SQL string is NULL. Should not occur!';
   END IF;
   
   -- RAISE NOTICE '%', _sql;       -- debug
   EXECUTE _sql;              -- execute
   GET DIAGNOSTICS updated_rows = ROW_COUNT; 
END
$func$;

CREATE FUNCTION


SELECT * FROM f_gap_fill_update('example', 'id', 'row_num');


nullable_columns
updated_rows


2
6



SELECT 1


TABLE example ORDER BY row_num;


row_num
id
str
val


1
1
1a
null

2
1
1a
1

3
2
2a
2

4
2
2a
2

5
3
null
null

6
3
3a
31

7
3
3a
31

8
3
3a
32

9
3
3b
32

10
3
3b
32



SELECT 10


-- fails (no nullable columns)
SELECT * FROM f_gap_fill_update('empty', 'id', 'row_num');

ERROR:  No nullable columns found in table >>empty<<
CONTEXT:  PL/pgSQL function f_gap_fill_update(regclass,text,text) line 33 at RAISE


-- fails (no nullable columns)
SELECT * FROM f_gap_fill_update('all_notnull', 'id', 'row_num');

ERROR:  No nullable columns found in table >>all_notnull<<
CONTEXT:  PL/pgSQL function f_gap_fill_update(regclass,text,text) line 33 at RAISE
row_num	id	str	val
1	1	1a	null
2	1	1a	1
3	2	2a	2
4	2	2a	2
5	3	null	null
6	3	3a	31
7	3	3a	31
8	3	3a	32
9	3	3b	32
10	3	3b	32