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

CREATE TABLE t1 (
  id   serial PRIMARY KEY
, col1 text
                            -- infowindow does not exist
);

INSERT INTO t1(col1) VALUES ('foo1'), ('bar1');

CREATE TABLE t2 (
  id   serial PRIMARY KEY
, col1 text
, infowindow text           -- column exists
);

INSERT INTO t2(col1, infowindow) VALUES ('foo2', 'here'), ('bar2', 'here');

CREATE TABLE

INSERT 0 2

CREATE TABLE

INSERT 0 2

-- function
CREATE OR REPLACE FUNCTION f_tbl_plus_infowindow (_tbl regclass) -- parameter name
  RETURNS void  -- no direct return type
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tmp text := quote_ident(_tbl::text || '_tmp');  -- apending _tmp for temp table
BEGIN
   -- Create temp table only for duration of transaction
   EXECUTE format(
      'CREATE TEMP TABLE %s ON COMMIT DROP AS
       SELECT * FROM %s LIMIT 0'
      ,_tmp, _tbl
     );

   IF EXISTS (
      SELECT FROM pg_attribute a
      WHERE  a.attrelid = _tbl
      AND    a.attname  = 'infowindow'
      AND    a.attisdropped = FALSE
      )
   THEN
      EXECUTE format('INSERT INTO %s SELECT * FROM %s', _tmp, _tbl);
   ELSE
     -- This is assuming a NOT NULL column named "id"!!
      EXECUTE format($$
         ALTER  TABLE %1$s ADD COLUMN infowindow text;
         INSERT INTO %1$s
         SELECT *, 'ID: ' || id::text
         FROM   %2$s$$
        ,_tmp, _tbl
        );
   END IF;
END
$func$;

CREATE FUNCTION

SELECT f_tbl_plus_infowindow ('t1');
SELECT * FROM t1_tmp;

f_tbl_plus_infowindow

SELECT 1

id
col1
infowindow

1
foo1
ID: 1

2
bar1
ID: 2

SELECT 2

SELECT f_tbl_plus_infowindow ('t2');
SELECT * FROM t2_tmp;

f_tbl_plus_infowindow

SELECT 1

id
col1
infowindow

1
foo2
here

2
bar2
here

SELECT 2