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

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay