Difficulty implementing some kind of backwards compatibility in PostgreSQL

Question

I'm facing an issue with implementing a rule or trigger in PostgreSQL to handle inserts into a table where one of the column types (a) has been changed and old queries . I've tried a few approaches, but I haven't been able to achieve the desired behavior.

Here's a simplified version of my scenario:

I have a table t1 with columns a and b:

CREATE TABLE t1 (
 a int,
 b int
);

I want to allow queries that come with another type for column a, such as a string, and replace it with another value, say 0. However, if an integer value is inserted, it should be preserved without modification.

I've tried using a rule and trigger function, but I'm encountering issues with each approach:

1. Rule: I tried creating a rule to intercept inserts and modify the value of column a if it's not an integer. However, I faced issues with infinite recursion and the rule not being triggered for invalid inputs:

CREATE RULE test_rule AS
 ON INSERT TO t1
 WHERE pg_typeof(NEW.a) <> 'integer'::regtype
 DO INSTEAD
 INSERT INTO t1 (a, b)
 VALUES (0, NEW.b)

postgres=# insert into t1 (select 'x',1);
ERROR: invalid input syntax for type integer: "x"
LINE 1: insert into t1 (select 'x',1);
 ^
postgres=# insert into t1 (select 1,1);
ERROR: infinite recursion detected in rules for relation "t1"

2. Trigger Function: I attempted to use a trigger function to check the data type of the inserted value and modify it accordingly. While this approach worked for valid inputs, it didn't handle invalid inputs as expected.

CREATE OR REPLACE FUNCTION insert_default_a_if_non_integer()
RETURNS TRIGGER AS
$$
BEGIN
 IF pg_typeof(NEW.a) <> 'integer'::regtype THEN
 NEW.a := 0; -- or any default value you prefer
 END IF;
 RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER t1_insert_trigger
BEFORE INSERT ON t1
FOR EACH ROW
EXECUTE FUNCTION insert_default_a_if_non_integer();

postgres=# insert into t1 (select 1,1);
INSERT 0 1
postgres=# insert into t1 (select 'x',1);
ERROR: invalid input syntax for type integer: "x"
LINE 1: insert into t1 (select 'x',1);
 ^

Could someone please provide guidance on the best approach to achieve my requirement? Any insights or alternative solutions would be greatly appreciated.

Thank you!

Answer 1

Your attempts fail at the statement compilation stage, before they begin executing and cause the rewrite rule or trigger to activate.

One way to address your predicament is to create a view with columns compatible with your now incorrect insert statements and define an instead of trigger on that view.

• Laurenz Albe: ... or have the application run different queries, depending on the schema version. That might be the simplest way.