DEV Community

Discussion on: Rule on insert

rhymes profile image
rhymes • Edited on

Your rule on insert triggers a rule on insert which triggers a rule on insert which triggers a rule on insert :-)

You might want to use a view if the goal is to have the app see {} instead of undefined or you could look into a trigger.

This works on PostgreSQL 11:

create table telegraf_session (id bigserial primary key, session text);

CREATE OR REPLACE FUNCTION telegraf_session_replace_undefined()
  RETURNS trigger AS $$
   IF NEW.session = 'undefined' THEN
     NEW.session := '{}';
   END IF;
$$ LANGUAGE plpgsql;

CREATE TRIGGER telegraf_session_replace_undefined_trigger
BEFORE INSERT ON telegraf_session
EXECUTE PROCEDURE telegraf_session_replace_undefined();

insert into telegraf_session (session) values ('valid');
insert into telegraf_session (session) values ('undefined');

and this is the output:

test=# select * from telegraf_session;
 id | session
  4 | valid
  5 | {}
(2 rows)
fcfn profile image
Peter Timoshevsky Author

Thanks a ton!

Do I understand correctly that it's impossible to have a rule on insert that does an insert to the same table as no matter the condition it will still trigger itself before the condition is checked thus giving the infinite recursion error?

rhymes profile image

I'm not 100% certain but as far as I know, yes it's not possible. The purpose of rules is to rewrite queries, sort of a macro system: "you write X and I write y". If the source and the target are the same, the rule itself gets re-activated.

A trigger instead is a precondition (or post condition) to a query.