3

I use a PostgreSQL 9.5.14 (PostGIS) and QGIS 2.18.7.

The problem is that when I try to add a new object to the View via a QGIS, the following error occurs:

ERROR: column "geom" is of type geometry but expression is of type integer LINE 1: INSERT INTO testtr VALUES (NEW.objectid, NEW.num_pdr, NEW.ge... ^ HINT: You will need to rewrite or cast the expression. QUERY: INSERT INTO testtr VALUES (NEW.objectid, NEW.num_pdr, NEW.geom) CONTEXT: PL/pgSQL function testtr_view_dml() line 4 at SQL statement

But update and delete triggers work properly. I think problem is in the trigger function for insert.

My query:

At first i create a table in pgAdmin with next query

-- Table: public.testtr

-- DROP TABLE public.testtr;

CREATE TABLE public.testtr
(
  id integer NOT NULL DEFAULT nextval('testtr_id_seq'::regclass),
  geom geometry(MultiPointZ,100000),
  num_pdr integer,
  CONSTRAINT testtr_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.testtr
OWNER TO postgres;
GRANT ALL ON TABLE public.testtr TO postgres;

Next I create a view for this table with query

-- View: public.testtr_view

-- DROP VIEW public.testtr_view;

CREATE OR REPLACE VIEW public.testtr_view AS 
 SELECT row_number() OVER (ORDER BY testtr.*) AS objectid,
    testtr.num_pdr,
    testtr.geom
FROM testtr;

ALTER TABLE public.testtr_view
OWNER TO postgres;
GRANT ALL ON TABLE public.testtr_view TO postgres;

-- Trigger: testtr_view_dml_trig on public.testtr_view

-- DROP TRIGGER testtr_view_dml_trig ON public.testtr_view;

CREATE TRIGGER testtr_view_dml_trig
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON public.testtr_view
  FOR EACH ROW
  EXECUTE PROCEDURE public.testtr_view_dml();

I need this view to be editable in QGIS so I create a trigger fuction

-- Function: public.testtr_view_dml()

-- DROP FUNCTION public.testtr_view_dml();

CREATE OR REPLACE FUNCTION public.testtr_view_dml()
  RETURNS trigger AS
$BODY$
    BEGIN
        IF TG_OP = 'INSERT' THEN
        INSERT INTO testtr VALUES (NEW.objectid, NEW.num_pdr, NEW.geom);
        RETURN NEW;

        ELSEIF TG_OP = 'UPDATE' THEN
        UPDATE testtr SET geom = NEW.geom WHERE id=OLD.objectid;
        RETURN NEW;

      END IF;
      RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.testtr_view_dml()
  OWNER TO postgres;

AND at last the trigger

-- Trigger: testtr_view_dml_trig on public.testtr_view

-- DROP TRIGGER testtr_view_dml_trig ON public.testtr_view;

CREATE TRIGGER testtr_view_dml_trig
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON public.testtr_view
  FOR EACH ROW
  EXECUTE PROCEDURE public.testtr_view_dml();
  • the error is actually quite self-explanatory, no? in your trigger function you insert the columns in the wrong order. btw. DELETE doesn't do anything in your setup. – ThingumaBob Oct 25 '18 at 8:46
  • @ThingumaBob You are right. Thank you. I changed it to (NEW.objectid, NEW.geom, NEW.num_pdr), but it caused a new error ERROR: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, 01040000A0A0860100010000000101000080E7FF885C43DFD340E269667A2A57..., 66). – Vladlena Oct 25 '18 at 8:54
  • id is defined as a self-incrementing sequence; you're not supposed to insert values in it. since NEW.objectid holds no value and is thus null, it violates the constraint. remove the NEW.objectid from the INSERT column list. – ThingumaBob Oct 25 '18 at 9:02
  • @ThingumaBob I tried to write INSERT INTO testtr VALUES (nextval('testtr_id_seq'::regclass), NEW.geom, NEW.num_pdr); and it works. Thank you to show the problem. – Vladlena Oct 25 '18 at 9:21

Your Answer

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

Browse other questions tagged or ask your own question.