Postgres 16

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

CREATE TABLE

INSERT 0 3

-- explicit cursor
CREATE OR REPLACE FUNCTION f_curs1(_tbl text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   _curs refcursor;
   rec record;
BEGIN
   OPEN _curs FOR
      EXECUTE 'SELECT * FROM ' || quote_ident(_tbl) FOR UPDATE;
   LOOP
      FETCH NEXT FROM _curs INTO rec;
      EXIT WHEN rec IS NULL;

      RAISE NOTICE '%', rec.tbl_id;

      EXECUTE format('UPDATE %I SET tbl_id = tbl_id + 10 WHERE ctid = $1', _tbl)
      USING rec.ctid;
   END LOOP;
END
$func$;

-- implicit FOR cursor
CREATE OR REPLACE FUNCTION f_curs2(_tbl text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   _ctid tid;
BEGIN
   FOR _ctid IN
      EXECUTE 'SELECT ctid FROM ' || quote_ident(_tbl) FOR UPDATE
   LOOP
      EXECUTE format('UPDATE %I SET tbl_id = tbl_id + 100 WHERE ctid = $1', _tbl)

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

SELECT f_curs1('tbl');
SELECT * FROM tbl;

f_curs1

SELECT 1

tbl_id
tbl

11
foo

12
bar

13
baz

SELECT 3

SELECT f_curs2('tbl');
SELECT * FROM tbl;

f_curs2

SELECT 1

tbl_id
tbl

111
foo

112
bar

113
baz

SELECT 3

SELECT f_nocurs('tbl');
SELECT * FROM tbl;

f_nocurs

SELECT 1

tbl_id
tbl

1111
foo

1112
bar

1113
baz

SELECT 3