Postgres 13

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(name text);
INSERT INTO t1 VALUES ('old'), ('not_old1');

CREATE TABLE t2(name text);
INSERT INTO t2 VALUES ('old'), ('not_old2');

CREATE TABLE

INSERT 0 2

CREATE TABLE

INSERT 0 2

-- just generate UPDATE statements:
CREATE OR REPLACE FUNCTION f_update_all(_old_val text, _new_val text, VARIADIC _tbls regclass[])
  RETURNS SETOF text
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tbl regclass;
BEGIN
   FOREACH _tbl IN ARRAY _tbls
   LOOP
      RETURN NEXT 'UPDATE ' || _tbl || ' SET name = $2 WHERE name = $1;';
   END LOOP;
END
$func$;

CREATE FUNCTION

SELECT f_update_all('old', 'NEW', 't1', 't2');

f_update_all

UPDATE t1 SET name = $2 WHERE name = $1;

UPDATE t2 SET name = $2 WHERE name = $1;

SELECT 2

-- dynamically execute:
DROP FUNCTION f_update_all(text, text, regclass[]);

CREATE OR REPLACE FUNCTION f_update_all(_old_val text, _new_val text, VARIADIC _tbls regclass[])
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tbl regclass;
BEGIN
   FOREACH _tbl IN ARRAY _tbls
   LOOP
      EXECUTE 'UPDATE ' || _tbl || ' SET name = $2 WHERE name = $1'
      USING $1, $2;
   END LOOP;
END
$func$;

DROP FUNCTION

CREATE FUNCTION

SELECT f_update_all('old', 'NEW', 't1', 't2');

f_update_all

SELECT 1

TABLE t1;

name

not_old1

NEW

SELECT 2

TABLE t2;

name

not_old2

NEW

SELECT 2