Case insensitive column names in a postgresql trigger function

Question

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?

• Colin 't Hart: You really would save yourself a lot of trouble in future by renaming the columns to lowercase. One time pain to avoid all of this jumping-through-hoops ever again.

Answer 1

Use format with the %I placeholder for identifiers:

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

Answer 2

Double-quote identifiers automatically where needed with format() (like Laurenz demonstrates), or quote_ident(). See:

• Are PostgreSQL column names case-sensitive?
• SQL injection in Postgres functions vs prepared queries

Also, your trigger function can be more efficient like this:

CREATE OR REPLACE FUNCTION my_latlon_function()
 RETURNS trigger
 LANGUAGE plpgsql AS
$func$
DECLARE
 _latcol text;
 _loncol text;
 _lat numeric;
 _lon numeric;
BEGIN
 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;
 
 EXECUTE concat('SELECT 1ドル.', _latcol, ', 1ドル.', _loncol)
 USING NEW
 INTO _lat, _lon;
 RAISE NOTICE '% / %', _lat, _lon; -- debug output
 -- do much stuff
 RETURN NEW;
END
$func$;

db<>fiddle here

(This is assuming column names always match unambiguously!?)

Demonstrating quote_ident(). With column names already quoted properly, we can simply concat() later.

The catalog table pg_catalog.pg_attribute is notably faster than information_schema.columns. Compare the output of these two commands to understand why:

EXPLAIN SELECT * FROM information_schema.columns;

EXPLAIN SELECT * FROM pg_catalog.pg_attribute;

See:

• Query to return output column names and data types of a query, table or view
• How do I list all columns for a specified table
• How to check if a table exists in a given schema

In this particular case, we can also conveniently use TG_RELID instead of TG_TABLE_SCHEMA and TG_TABLE_NAME.

Also, individual assignments are comparatively expensive in PL/pgSQL. Try to keep their number low.

While the difference in performance is still small, this trigger function is called once per inserted row. Adds up for big inserts ...

Aside, col ~* 'lon' is exactly equivalent to col ~* '.*lon.*'.