1

I'm updating data with QGis on a Postgres table. The operation consists of splitting a polygon in two parts, which means that QGis updates the original polygon (to become one of the 2 new ones) and creates a second one. On save the result is stored in the table table1 as 2 records, one with the original id (the PK) and the other with a new id. But I need to keep the original polygon (for history) and I try to do this with a trigger function that simply should make a copy of the original record from table1 to another table table2, totally similar to the first one, except that I removed the SERIAL and the PK.

My trigger:

CREATE TRIGGER area_reduc_1
    BEFORE UPDATE
    ON table1
    FOR EACH ROW
    WHEN ((pg_trigger_depth() < 1))
    EXECUTE PROCEDURE save_area_before_upd();

My trigger function is:

CREATE OR REPLACE FUNCTION field_survey.save_area_before_upd()
    RETURNS trigger AS
$BODY$
BEGIN

IF (TG_TABLE_NAME = 'table1') AND (TG_OP = 'UPDATE') THEN 

    INSERT INTO table2
    SELECT DISTINCT * 
    FROM table1 d
    WHERE NEW."id" = d."id" AND NEW.area_ref = d.area_ref
    ;
END IF;  

RETURN NEW;

END;
$BODY$

BUT in table2 I end up with attribute values that are the ones I recorded in QGis, i.e. the ones coming from NEW! Why and how can I solve this?

I use QGis 2.8.2 (but that should not matter) and PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit.

2
  • are you looking for OLD.id ...? Jul 23, 2015 at 18:59
  • God !! yes ! It seems so obvious now. I'm out of my office, I will try tomorrow. But still, why didn't my solution work ? Thank you very much, @Aneesh Mon N
    – dd_a
    Jul 23, 2015 at 19:18

1 Answer 1

1

Use record type OLD to access the old values as you do NEW to access the new values when updating a record in a table.

Refer: http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html

Not the answer you're looking for? Browse other questions tagged or ask your own question.