Postgres 17

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 tbl (id int, txt text, val int);
INSERT INTO tbl VALUES
  (1, 'foo', 3)
, (2, 'bar', 4)
;

CREATE OR REPLACE FUNCTION change_val(_tbl_type anyelement)
  RETURNS SETOF anyelement  -- problem solved
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   format('UPDATE %s SET val = 2 RETURNING *;', pg_typeof(_tbl_type));
END
$func$;

CREATE TABLE

INSERT 0 2

CREATE FUNCTION

TABLE tbl;

id
txt
val

1
foo
3

2
bar
4

SELECT 2

SELECT * FROM change_val(NULL::tbl);

id
txt
val

1
foo
2

2
bar
2

SELECT 2

TABLE tbl;

id
txt
val

1
foo
2

2
bar
2

SELECT 2