Postgres 12

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 TYPE state AS (last_active timestamp);
CREATE TABLE devices (state state);

CREATE OR REPLACE FUNCTION update_last_active_on_position_update_hook()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.state IS NOT NULL THEN
      NEW.state.last_active := now();
   END IF;
   RETURN NEW;
END
$func$;

CREATE TRIGGER foo
BEFORE UPDATE ON devices
FOR EACH ROW
-- WHEN (NEW.state IS NOT NULL)
EXECUTE FUNCTION update_last_active_on_position_update_hook();
ERROR:  "new.state.last_active" is not a known variable
LINE 10:       NEW.state.last_active := now();
               ^


INSERT INTO devices VALUES ('("2021-11-11 11:11:21")')
RETURNING *;
ERROR:  relation "devices" does not exist
LINE 1: INSERT INTO devices VALUES ('("2021-11-11 11:11:21")')
                    ^


UPDATE devices
SET    state.last_active = '2000-12-04 15:10:29'
RETURNING *;
ERROR:  relation "devices" does not exist
LINE 1: UPDATE devices
               ^