How to use variable settings in trigger functions?
It is not clear why you are trying to concat NULL
to user_id
but it is obviously the cause of the problem. Get rid of it:
CREATE OR REPLACE FUNCTION add_transition1() RETURNS TRIGGER AS $$ DECLARE user_id integer; BEGIN user_id := current_setting('myvars.user_id')::integer; INSERT INTO transitions1 (user_id, house_id) VALUES (user_id, NEW.id); RETURN NULL; END;$$ LANGUAGE plpgsql;
Note that
SELECT 55 || NULL
always gives NULL
.
Handle all possible cases for the customized option properly:
option not set yet
All references to it raise an exception, including
current_setting()
unless called with the second parametermissing_ok
. The manual:If there is no setting named
setting_name
,current_setting
throws an error unlessmissing_ok
is supplied and istrue
.option set to a valid integer literal
option set to an invalid integer literal
option reset (which burns down to a special case of 3.)
For instance, if you set a customized option with
SET LOCAL
orset_config('myvars.user_id3', '55', true)
, the option value is reset at the end of the transaction. It still exists, can be referenced, but it returns an empty string now (''
) - which cannot be cast tointeger
.
Obvious mistakes in your demo aside, you need to prepare for all 4 cases. So:
CREATE OR REPLACE FUNCTION add_transition1() RETURNS trigger AS$func$DECLARE _user_id text := current_setting('myvars.user_id', true); -- see 1.BEGIN IF _user_id ~ '^\d+$' THEN -- one or more digits? INSERT INTO transitions1 (user_id, house_id) VALUES (_user_id::int, NEW.id); -- valid int, cast is safe ELSE INSERT INTO transitions1 (user_id, house_id) VALUES (NULL, NEW.id); -- use NULL instead RAISE WARNING 'Invalid user_id % for house_id % was reset to NULL!' , quote_literal(_user_id), NEW.id; -- optional END IF; RETURN NULL; -- OK for AFTER triggerEND$func$ LANGUAGE plpgsql;
db<>fiddle here
Notes:
Avoid variable names that match column names. Very error prone. One popular naming convention is to prepend variable names with an underscore:
_user_id
.Assign at declaration time to save one assignment. Note the data type
text
. We'll cast later, after sorting out invalid input.Avoid raising / trapping an exception if possible. The manual:
A block containing an
EXCEPTION
clause is significantly more expensive to enter and exit than a block without one. Therefore, don't useEXCEPTION
without need.Test for valid integer strings. This simple regular expression allows only digits (no leading sign, no white space):
_user_id ~ '^\d+$'
. I reset to NULL for any invalid input. Adapt to your needs.I added an optional
WARNING
for your debugging convenience.Cases
3.
and4.
only arise because customized options are string literals (typetext
), valid data types cannot be enforced automatically.
Related:
- User defined variables in PostgreSQL
- Is there a way to define a named constant in a PostgreSQL query?
All that aside, there may be more elegant solutions for what you are trying to do without customized options, depending on your exact requirements. Maybe this:
You can catch the exception when the value doesn't exist - here's the changes I made to get this to work:
CREATE OR REPLACE FUNCTION add_transition1() RETURNS TRIGGER AS $$ DECLARE user_id integer; BEGIN BEGIN user_id := current_setting('myvars.user_id')::integer; EXCEPTION WHEN OTHERS THEN user_id := 0; END; INSERT INTO transitions1 (user_id, house_id) VALUES (user_id, NEW.id); RETURN NULL; END;$$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION insert_house() RETURNS void as $$ DECLARE user_id integer; BEGIN PERFORM set_config('myvars.user_id', '55', false); INSERT INTO houses (name) VALUES ('HOUSE PARTY'); END; $$ LANGUAGE plpgsql;