Postgres 15

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

-- trigger function for UPDATE
CREATE OR REPLACE FUNCTION f_trg_up()
  RETURNS TRIGGER
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tbl  regclass := quote_ident(TG_TABLE_SCHEMA) || '.'
                  || quote_ident(substring(TG_TABLE_NAME from '(.+)_view$'));
   _cols text;
   _vals text;
BEGIN
   SELECT INTO _cols, _vals
          string_agg(quote_ident(attname), ', ')
        , string_agg('x.' || quote_ident(attname), ', ')
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    NOT attisdropped   -- no dropped (dead) columns
   AND    attnum > 0;        -- no system columns

EXECUTE format('
   UPDATE %s
   SET   (%s) = (%s)
   FROM  (SELECT ($1).*) x', _tbl, _cols, _vals)
   USING NEW;

RETURN NEW; -- Don't return NULL unless you knwo what you're doing
END
$func$;

-- trigger function for INSERT
CREATE OR REPLACE FUNCTION f_trg_ins()
  RETURNS TRIGGER
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tbl regclass := quote_ident(TG_TABLE_SCHEMA) || '.'
                 || quote_ident(substring(TG_TABLE_NAME FROM '(.+)_view$'));
BEGIN
   EXECUTE format('INSERT INTO %s SELECT ($1).*', _tbl)
   USING NEW;

RETURN NEW;  -- Don't return NULL unless you know what you're doing
END
$func$;

-- TABLE and VIEW
CREATE TABLE a(id int, txt text, day date);
CREATE VIEW a_view AS SELECT * FROM a;

-- Triggers
CREATE TRIGGER trg_instead_up
INSTEAD OF UPDATE ON a_view
FOR EACH ROW EXECUTE FUNCTION f_trg_up();

CREATE TRIGGER trg_instead_ins
INSTEAD OF INSERT ON a_view
FOR EACH ROW EXECUTE FUNCTION f_trg_ins();

-- INSERT row
INSERT INTO a VALUES (1, 'foo', current_date);

CREATE FUNCTION

CREATE FUNCTION

CREATE TABLE

CREATE VIEW

CREATE TRIGGER

CREATE TRIGGER

INSERT 0 1

UPDATE 1

INSERT 0 1

id	txt	day
1	foo_updated	2024-02-26
2	baz_inserted	2011-11-11

SELECT 2