Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE houses (
   id         serial PRIMARY KEY
 , name       varchar(80)
 , created_at timestamp DEFAULT now()
);

CREATE TABLE transitions1 (
   id          serial PRIMARY KEY
 , house_id    integer
 , user_id     integer
 , created_at  timestamp DEFAULT now()
 , FOREIGN KEY(house_id) REFERENCES houses (id) ON DELETE CASCADE
);

CREATE OR REPLACE FUNCTION add_transition1()
  RETURNS trigger AS
$func$
DECLARE
   _user_id text := current_setting('myvars.user_id', true);  -- text!
BEGIN
   IF _user_id ~ '^\d+$' THEN  -- one or more digits?
      -- all good, do nothing
   ELSE
      RAISE WARNING 'Invalid user_id % for house_id % was reset to NULL!', _user_id, NEW.id;  -- optional
      _user_id := NULL;  -- or do something else?
   END IF;

   INSERT INTO transitions1 (user_id, house_id)
   VALUES (_user_id::int, NEW.id);  -- cast is safe now

   RETURN NULL;  -- OK for AFTER trigger
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER add_transition1
AFTER INSERT OR UPDATE ON houses
FOR EACH ROW EXECUTE PROCEDURE add_transition1();

CREATE TABLE

CREATE TABLE

CREATE FUNCTION

CREATE TRIGGER

-- case 1: option not set yet
INSERT INTO houses (name)
VALUES ('HOUSE PARTY 1')
RETURNING houses.id;

id

1

INSERT 0 1

-- case 2: option set to valid int
BEGIN;
SELECT set_config('myvars.user_id', '55', true); -- SET LOCAL

INSERT INTO houses (name)
VALUES ('HOUSE PARTY 2')
RETURNING houses.id;
COMMIT;

BEGIN

set_config

55

SELECT 1

id

2

INSERT 0 1

COMMIT

-- case 3: option set to invalid int
BEGIN;
SELECT set_config('myvars.user_id', '5x5', true); -- SET LOCAL

INSERT INTO houses (name)
VALUES ('HOUSE PARTY 3')
RETURNING houses.id;
COMMIT;

BEGIN

set_config

5x5

SELECT 1

id

3

INSERT 0 1

COMMIT

-- case 4: option reset (empty string)
INSERT INTO houses (name)
VALUES ('HOUSE PARTY 4')
RETURNING houses.id;

id

4

INSERT 0 1

TABLE houses;
TABLE transitions1;

id
name
created_at

1
HOUSE PARTY 1
2025-04-22 18:03:11.567646

2
HOUSE PARTY 2
2025-04-22 18:03:11.587839

3
HOUSE PARTY 3
2025-04-22 18:03:11.594737

4
HOUSE PARTY 4
2025-04-22 18:03:11.596582

SELECT 4

id
house_id
user_id
created_at

1
1
null
2025-04-22 18:03:11.567646

2
2
55
2025-04-22 18:03:11.587839

3
3
null
2025-04-22 18:03:11.594737

4
4
null
2025-04-22 18:03:11.596582

SELECT 4

id	name	created_at
1	HOUSE PARTY 1	2025-04-22 18:03:11.567646
2	HOUSE PARTY 2	2025-04-22 18:03:11.587839
3	HOUSE PARTY 3	2025-04-22 18:03:11.594737
4	HOUSE PARTY 4	2025-04-22 18:03:11.596582

id	house_id	user_id	created_at
1	1	null	2025-04-22 18:03:11.567646
2	2	55	2025-04-22 18:03:11.587839
3	3	null	2025-04-22 18:03:11.594737
4	4	null	2025-04-22 18:03:11.596582