kartoza-geek kartoza-geek - 29 days ago 20
SQL Question

Update a table column using postgres function

I have a table called

base
which I need to update after a record has been inserted. I have written a function.

CREATE OR REPLACE FUNCTION host_ip() RETURNS trigger AS $host_ip$
BEGIN

update base set thumbnail_url =
replace ("thumbnail_url",'localhost','myipadd') WHERE id = NEW.id;
RETURN NEW;
END;
$host_ip$ LANGUAGE plpgsql;


This function is giving me errors when a record is inserted. I have a trigger which calls the function after insert. The error is
PL/pgSQL function host_ip() line 4 at SQL statement SQL statement


The trigger is:

CREATE TRIGGER host_ip AFTER INSERT OR UPDATE ON base
FOR EACH ROW EXECUTE PROCEDURE host_ip();

Answer

To change the column of the just inserted or updated row, you only need to assign the value. There is no need to run an update.

CREATE OR REPLACE FUNCTION host_ip() RETURNS trigger 
AS 
$host_ip$
BEGIN
   new.thumbnail_url := replace(thumbnail_url,'localhost','myipadd');
   RETURN NEW;
END;
$host_ip$ 
LANGUAGE plpgsql;

But you can't change the value in an AFTER trigger, you need a BEFORE trigger for this:

CREATE TRIGGER host_ip 
   BEFORE INSERT OR UPDATE ON base
FOR EACH ROW EXECUTE PROCEDURE host_ip();