Postgresql create stored procedure with update a row

Question

I have a table names Locations. It has columns [ID, GUID_ID, NAME, GEOMETRY]. I want to update GUID_ID column after a row inserted. So I will create a trigger to do this.

CREATE TRIGGER update_id 
   AFTER INSERT ON Locations
   ?? (How can Update GUID_ID column as uuid_generate_v4())

I set a default value for this column. But third party applications like QGIS inserts thousands of geometry records at same time. So the default value does not fill all columns. They are null. So I need trigger for solution.

lat long · Accepted Answer · 2017-12-26 07:46:32Z

1

I think it dosnt matter how much records QGIS enters, if you have defined a default value for field. May be QGIS is entering empty space instead. You can also add "NOT NULL" constraint in the field definition. However trigger can be used like this. I am setting the GUID_ID value BEFORE INSERTING though.

CREATE TRIGGER update_id 
   BEFORE INSERT ON Locations
   FOR EACH ROW EXECUTE PROCEDURE fn_trgr();

CREATE OR REPLACE FUNCTION fn_trgr() RETURNS TRIGGER AS $$
BEGIN
    IF pg_trigger_depth() <> 1 THEN
        RETURN NEW;
    END IF;
    NEW.GUID_ID = uuid_generate_v4(); -- or whatever value you wants to set
    RETURN NEW;

END;
$$ LANGUAGE plpgsql;

answered Dec 26, 2017 at 7:46

lat long

9306 silver badges16 bronze badges

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

6 Comments

barteloma Over a year ago

I will update only inserted row. I think qgis sends as null GUID_ID field.

lat long Over a year ago

Then just add a "NOT NULL" constraint in column of table DDL

barteloma Over a year ago

How can I check if GUID_ID column exist?

lat long Over a year ago

from the definition of your table Locations

barteloma Over a year ago

Does matter guid id uppercase or lowercase?

|

Collectives™ on Stack Overflow

Postgresql create stored procedure with update a row

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related