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

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

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