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 OR REPLACE FUNCTION my_latlon_function()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   _latcol text;
   _loncol text;
   _lat numeric;
   _lon numeric;
BEGIN
   -- assuming both matches are unambiguous !?
   SELECT quote_ident(a.attname) INTO _latcol  -- properly quoted!
   FROM   pg_attribute a
   WHERE  a.attrelid = TG_RELID
   AND    a.attname ~* 'lat'
   AND    a.attnum > 0
   AND    NOT a.attisdropped;

   SELECT quote_ident(a.attname) INTO _loncol
   FROM   pg_attribute a
   WHERE  a.attrelid = TG_RELID
   AND    a.attname ~* 'lon'
   AND    a.attnum > 0
   AND    NOT a.attisdropped;
   
   -- column names already quoted properly, we can simply concat:
   EXECUTE concat('SELECT $1.', _latcol, ', $1.', _loncol)
   USING  NEW
   INTO   _lat, _lon;

-- RAISE NOTICE '% / %', _lat, _lon;  -- debug output
-- EXCEPTION to make it visible in the fiddle
   RAISE EXCEPTION 'debug output lat / lon: % / %', _lat, _lon;  -- debug output

   -- do much stuff

   RETURN NEW;
END
$func$;

CREATE FUNCTION


-- test table & trigger
CREATE TABLE latlon (
  id              serial PRIMARY KEY
, some_latitude   numeric
, "Longitude col" numeric
);


CREATE TRIGGER ins_aft
AFTER INSERT ON latlon
FOR EACH ROW
EXECUTE PROCEDURE my_latlon_function();

CREATE TABLE



CREATE TRIGGER


INSERT INTO latlon(some_latitude, "Longitude col") VALUES (1.23, 4.56);

ERROR:  debug output lat / lon: 1.23 / 4.56
CONTEXT:  PL/pgSQL function my_latlon_function() line 30 at RAISE