sql

-- BASIC TABLE

DROP TABLE IF EXISTS test CASCADE;
CREATE TABLE test (
    id serial NOT NULL PRIMARY KEY,
    name text,
    geom geometry(MultiPolygon,4326)
);

-- BASIC VIEW

DROP VIEW IF EXISTS test_view CASCADE;
CREATE VIEW test_view AS (
    SELECT * FROM test
);
ALTER VIEW test_view ALTER COLUMN id SET DEFAULT nextval('test_id_seq'::regclass);

-- FUNCTIONS THAT DO THE INSERT/UPDATE/DELETE

DROP FUNCTION IF EXISTS view_ins() CASCADE;
CREATE FUNCTION view_ins() RETURNS trigger AS    
$$
    BEGIN
          INSERT INTO test( id, name, geom )
          VALUES ( NEW.id, NEW.name, NEW.geom )
          RETURNING id INTO NEW.id; -- we add this to make RETURNING id work
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS view_up() CASCADE;
CREATE FUNCTION view_up() RETURNS trigger AS    
$$
    BEGIN
          UPDATE test SET
              id=NEW.id,
              name=NEW.name,
              geom=NEW.geom
        WHERE id=OLD.id;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS view_del() CASCADE;
CREATE FUNCTION view_del() RETURNS trigger AS    
$$
    BEGIN
        DELETE FROM test WHERE id=OLD.id;
        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;


-- TRIGGERS TO RUN THE FUNCTIONS ON THE BASE TABLE INSTEAD OF THE VIEW

CREATE TRIGGER view_trigger_ins INSTEAD OF INSERT ON test_view FOR EACH ROW
    EXECUTE PROCEDURE view_ins();

CREATE TRIGGER view_trigger_up INSTEAD OF UPDATE ON test_view FOR EACH ROW
    EXECUTE PROCEDURE view_up();

CREATE TRIGGER view_trigger_del INSTEAD OF DELETE ON test_view FOR EACH ROW
    EXECUTE PROCEDURE view_del();