DEV Community

Ahmad Ya'kob Ubaidullah
Ahmad Ya'kob Ubaidullah

Posted on

Postgres pre modify data before inserts

Let say we want to modify data with only matching text *redirect* in the rpz_zone column, we first create the function like below:

CREATE OR REPLACE FUNCTION public.update_redirect_rpz()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
IF NEW.rpz_zone like '%redirect%' THEN
    NEW.redirect = true;
END IF;
RETURN NEW;
END;
Enter fullscreen mode Exit fullscreen mode

then we can check the function with \ef update_redirect_rpz

after this we create a trigger to hook this method to an event like below:

   CREATE TRIGGER bind_rpz_log_redirect_flag
   BEFORE INSERT ON bind_rpz_log
   FOR EACH ROW EXECUTE PROCEDURE update_redirect_rpz();
Enter fullscreen mode Exit fullscreen mode

Incase something went wrong, you can always check your log like in my case it is like this tail -f -n100 /var/log/messages.

Feb 22 19:02:13 dnsvgitnlognode postgres[39307]: [28-1] 2021-02-22 19:02:13.682 +08 [39307] ERROR:  syntax error at or near "update_redirect_rpz" at character 1
Feb 22 19:02:13 dnsvgitnlognode postgres[39307]: [28-2] 2021-02-22 19:02:13.682 +08 [39307] STATEMENT:  update_redirect_rpz
Feb 22 19:02:13 dnsvgitnlognode postgres[39307]: [28-3]     ();
Feb 22 19:02:18 dnsvgitnlognode postgres[39307]: [29-1] 2021-02-22 19:02:18.202 +08 [39307] ERROR:  syntax error at or near "function" at character 1
Feb 22 19:02:18 dnsvgitnlognode postgres[39307]: [29-2] 2021-02-22 19:02:18.202 +08 [39307] STATEMENT:  function update_redirect_rpz();
Enter fullscreen mode Exit fullscreen mode

Discussion (0)