By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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
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 | 2024-10-21 09:11:35.192071 |
| 2 | HOUSE PARTY 2 | 2024-10-21 09:11:35.202044 |
| 3 | HOUSE PARTY 3 | 2024-10-21 09:11:35.204451 |
| 4 | HOUSE PARTY 4 | 2024-10-21 09:11:35.206354 |
SELECT 4
| id | house_id | user_id | created_at |
|---|---|---|---|
| 1 | 1 | null | 2024-10-21 09:11:35.192071 |
| 2 | 2 | 55 | 2024-10-21 09:11:35.202044 |
| 3 | 3 | null | 2024-10-21 09:11:35.204451 |
| 4 | 4 | null | 2024-10-21 09:11:35.206354 |
SELECT 4