4

Updating views from QGIS doesn't work as I expect it to work.

I've got the following (simplified) tables, views and triggers:

    CREATE TABLE public.parcels
    (
      id serial,
      geom geometry(MultiPolygon,27700),
      "Land Registry Reference" character varying(30),
      type integer,
      area double precision,
      "Design Issues" text,
      sys_period tstzrange NOT NULL DEFAULT tstzrange(now(), NULL::timestamp with time zone),
      CONSTRAINT parcels_pkey PRIMARY KEY (id)
    )
    WITH (
      OIDS=FALSE
    );

    CREATE TABLE public.owners
    (
      id integer,
      "Title Number" character varying,
      "Proprietor Name" character varying,
      sys_period tstzrange
    )
    WITH (
      OIDS=FALSE
    );

    drop view if exists from_two;

    CREATE VIEW from_two as(
      select "parcels"."id" AS "id",
      "parcels"."Land Registry Reference",
      "parcels"."geom",
      "owners"."Proprietor Name",
      "parcels"."Design Issues",
       ROW_NUMBER() over() AS uid
    FROM "public"."parcels"
    LEFT OUTER JOIN "owners" ON "parcels"."Land Registry Reference" = "owners"."Title Number"
    );

    CREATE OR REPLACE function update_from_two()
      returns trigger
      AS $f$
      BEGIN
        UPDATE public.parcels
        SET
        id = NEW.id,
        "Land Registry Reference" = NEW."Land Registry Reference",
        geom = NEW.geom,
        "Design Issues" = NEW."Design Issues"
        WHERE id = OLD.id;

      UPDATE owners
      SET "Proprietor Name" = NEW."Proprietor Name"
      WHERE "Proprietor Name" = OLD."Proprietor Name";

    return NEW;
    END;
    $f$ LANGUAGE plpgsql;

    DROP trigger if exists tg_fromtwo on from_two;

    CREATE  TRIGGER  tg_fromtwo
    INSTEAD OF UPDATE
    ON from_two
    FOR EACH ROW
      EXECUTE PROCEDURE update_from_two();

it all works fine and when I update the view in pgadmin with

UPDATE from_two
SET 
  id = 244,
  "Design Issues" = 'None'
WHERE id = 244;

I also can open this view in Qgis, but when I edit data and save it, the edited data ends up in the wrong table row. Do I have to do something different to be able to update correctly in Qgis?

I'm using Postgres 9.5, Postgis 2.2.2 and QGIS 2.16.2

2

In the end I found out what went wrong, the view needs to be sorted, so that the order is always the same. To make clear where all data comes from, I also created an combined ID:

    CREATE VIEW from_two as(
      select
        concat("parcels"."id"::text, '-', "owners"."id"::text) as id,
        "parcels"."id" as parcels_id,
        "owners"."id" as owners_id,
        "parcels"."Land Registry Reference",
        "parcels"."geom",
        "owners"."Proprietor Name",
        "parcels"."Design Issues",
        ROW_NUMBER() over() AS uid
      FROM "public"."parcels"
      LEFT OUTER JOIN "owners" ON "parcels"."Land Registry Reference" = 
                                                        "owners"."Title Number"
      ORDER BY id);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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