Postgresql – Case insensitive column names in a postgresql trigger function

case sensitiveplpgsqlpostgresqltrigger

I have a trigger function in PostgreSQL 12 which executes something like this:

CREATE OR REPLACE FUNCTION "my_latlon_function"()
RETURNS trigger AS
$$
BEGIN
    latcolumn:= (
        SELECT column_name
          FROM information_schema.columns
         WHERE table_schema = TG_TABLE_SCHEMA
           AND table_name = TG_TABLE_NAME
           AND column_name ~* '.*lat.*'
    );
    loncolumn := (
        SELECT column_name
          FROM information_schema.columns
         WHERE table_schema = TG_TABLE_SCHEMA
           AND table_name = TG_TABLE_NAME
           AND column_name ~* '.*lon.*'
    );
    EXECUTE 'select $1.' || loncolumn USING NEW INTO lon;
    EXECUTE 'select $1.' || latcolumn USING NEW INTO lat;

    -- do much stuff

    RETURN NEW;

END
$$
LANGUAGE 'plpgsql';

The problem is, the lat and lon columns have capital letters in their name, e.g. myLatitude and myLongitude. The trigger function is able to retrieve these names, that's no problem.

The problem lies in the two EXECUTE statements where it seems that the column names become lower-cased as stated by this error (in the underlying QUERY when the trigger is fired):

ERROR:  column "mylongitude" not found in data type gpspoints
LINE 1: select $1.myLongitude
               ^
QUERY:  select $1.myLongitude
CONTEXT:  PL/pgSQL function my_latlon_function() line 24 at EXECUTE
********** Error **********

ERROR: column "mylongitude" not found in data type gpspoints
SQL state: 42703
Context: PL/pgSQL function my_latlon_function() line 24 at EXECUTE

I know that PostgreSQL does need to enclose column names which have capital letters in their name into double quotes. Hence, I have tried to set double quotes in the two EXECUTE statements such as this:

    EXECUTE 'select $1.' || "loncolumn" USING NEW INTO lon;
    EXECUTE 'select $1.' || "latcolumn" USING NEW INTO lat;

But the error stays exactly the same.

If possible, how can I handle CamelCase column names in a PostgreSQL trigger function?

If not not, why?

Best Answer

Use format with the %I placeholder for identifiers:

EXECUTE format('select $1.%I', latcolumn) USING NEW INTO lat;