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: