Postgresql: How to access camelcased keys from Record structured in function triggers

Question

I'm using postgres 9.5, and defining a trigger to be run when a row is added to a table, something in the line of:

CREATE TRIGGER addedTrigger AFTER INSERT ON ships 
        FOR EACH ROW EXECUTE PROCEDURE added();

My added function is written in plpgsql, and its purpose is to fetch some data, assemble it into a certain format, and send over pg_notify to a channel. This is something resembling the body:

$$
BEGIN
PERFORM pg_notify('events', json_build_object('type', 'ADDED', 'userId', NEW.userId)::text);
RETURN NULL;
END;
$$

However, when the trigger runs, I get the following error message:

ERROR:  record "new" has no field "userid"
CONTEXT:  SQL statement "SELECT NEW.userId"
PL/pgSQL function added() line 5 at RAISE

Somehow the plpgsql function is downcasing my key annotation internally, and I'm not sure exactly how to proper use this, as all the examples I see in the wild are with non-primary-or-foreign-key attributes, and all of them are downcased.

error from friendregistered(), but you have added() in trigger - smth you dont tell us?.. — Vao Tsun
there is mixed case in NEW.userId - if you have it indeed, then change to NEW."userId" — Vao Tsun
Nice, fixed! Can I ask why this happens? And also, could you post this as an answer, so I can give you proper credit? — ChuckE

Vao Tsun Vao Tsun · Accepted Answer · 2017-06-01T09:47:45

ERROR: record "new" has no field "userid"

Is indeed a right error - there is no column "userid" (see the name is different in error then in your code). If you use a mixed case (or start identifier with number or special character or use any other not common name) you have to double quote the identifier, so NEW."userId" should work for you.

https://www.postgresql.org/docs/current/static/tutorial-table.html

SQL is case insensitive about key words and identifiers, except when identifiers are double-quoted to preserve the case

Postgresql: How to access camelcased keys from Record structured in function triggers

1 Answers