Update a table column using postgres function

Question

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();

I have added the trigger

kartoza-geek
– kartoza-geek

2016-11-09 08:26:40 +00:00
Commented Nov 9, 2016 at 8:26 — kartoza-geek
– kartoza-geek, Commented Nov 9, 2016 at 8:26

user330315 · Accepted Answer · 2016-11-09 08:30:08Z

1

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();

answered Nov 9, 2016 at 8:30

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update a table column using postgres function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related