[Qgis-user] Editing a Postgis view

Wed Nov 16 14:10:11 PST 2016

I think it's not difficult to use rules. The good thing is having such a
simple mechanism inside basic sql, without having to use a procedural
language to write functions and triggers to manage views. Although rules
are NOT standard SQL and sometimes, when you discard something for
convenience, you'll miss it in the future. So someone says that rules are
evil... I love them and they are a basic building block for PostgreSQL.
I assume that you have to insert into the view.
In this case the rule is fairly simple. To write the statement you have to
understand how the sequence works: if you have a NULL in the insert (I mean
during the insert into the table, not into the view) the sequence will be
activated and the next number in the sequence will be used instead of NULL.
So you have two possibilities for your rule:
1. Remove the ID from your column list (just list all the column besides
the ID);
2. Replicate the same action done when inserting in the base table;
and I found a third here
http://stackoverflow.com/questions/12836043/inserting-a-record-into-a-table-with-a-column-declared-with-the-serial-function
using DEFAULT instead of the field value.

So the rule will be something like this (I usually make mistakes the first
time, sorry in advance...):
CREATE RULE v_repository_point_ins AS
ON INSERT TO schemaapp.v_repository_point
DO INSTEAD
INSERT INTO schemaapp.repository (uuid, progetto, procedimento, *geometria*)
VALUES (new.uuid, new.progetto, new.procedimento, new.*geometria1*)
;
if you choose to let the base table do its work ;.).  Or
CREATE RULE v_repository_point_ins AS
ON INSERT TO schemaapp.v_repository_point
DO INSTEAD
INSERT INTO schemaapp.repository (id, uuid, progetto, procedimento,
geometria)
VALUES (nextval('repository_id_seq'), new.uuid, new.progetto,
new.procedimento, new.geometria1)
;
assuming the serial macro created a sequence with that name. Or
CREATE RULE v_repository_point_ins AS
ON INSERT TO schemaapp.v_repository_point
DO INSTEAD
INSERT INTO schemaapp.repository (id, uuid, progetto, procedimento,
geometria)
VALUES (DEFAULT, new.uuid, new.progetto, new.procedimento, new.geometria1)
;
if you choose the third mechanism.

I would use the first rule for three good reasons:
1. less elements in your statement means less to mistake for
2. do not force a thing that is already supposed to work
3. I trust the light into the fridge to switch off when I close the door.
If it does not work, better stop and think what's wrong in the database
instead of taking an hammer and get it done forcefully.
c

On Wed, Nov 16, 2016 at 8:36 PM, Andrea Peri <aperi2007 at gmail.com> wrote:

> Thx for clarification.
> My postgres is a 9.5 version. This explain why it has writable view.
>
> So i Need to try the role Instead of sinpky and often used serial type for
> pk.
>
> A.
>
> Il 16 nov 2016 19:22, "Nyall Dawson" <nyall.dawson at gmail.com> ha scritto:
>
>> On 17 Nov 2016 12:54 AM, "Carlo A. Bertelli (Charta s.r.l.)" <
>> carlo.bertelli at gmail.com> wrote:
>> >
>> > Ciao Andrea,
>> > by default Postgresql views are read only. Even the simplest view made
>> in Postgresql needs a rule to be updated.
>>
>> That's not quite correct. Recent versions of postgres make simple views
>> editable.
>> See
>>
>> https://www.depesz.com/2012/12/11/waiting-for-9-3-support-au
>> tomatically-updatable-views/
>>
>> Nyall
>>
>> After writing a statement for a view, just take the habit of writing the
>> rules for insert, update, delete.
>> > Please read the chapter on the Rule System in the Postgresql Reference
>> Manual for the instructions.
>> > c
>> >
>> > Message: 6
>> > Date: Wed, 16 Nov 2016 09:39:23 +0100
>> > From: Andrea Peri <aperi2007 at gmail.com>
>> > To: Matthias Kuhn <matthias at opengis.ch>
>> > Cc: qgis-user <qgis-user at lists.osgeo.org>
>> > Subject: Re: [Qgis-user] Editing a Postgis view
>> > Message-ID:
>> >         <CABqTJk8Bo7fs6qOG=MhO9N50qO5L+JkXAOAgik=tUGWwcKd-WA at mail.
>> gmail.com>
>> > Content-Type: text/plain; charset=UTF-8
>> >
>> > Hi Matthias,
>> > thx for response.
>> > So the trouble is in my db.
>> >
>> > Now I try to understand better why it is not working.
>> >
>> > This is my actual try test:
>> >
>> > create table schemaapp.repository(
>> >  ID serial primary key NOT NULL,
>> >  UUID varchar(50) NOT NULL,
>> >  PROGETTO varchar(9) NOT NULL,
>> >  PROCEDIMENTO text NOT NULL,
>> >  GEOMETRIA1 geometry(MULTIPOINT, 3003)
>> > );
>> >
>> > create view schemaapp.v_repository_point as
>> >  select
>> >   a.id as id,
>> >   a.uuid as uuid,
>> >   a.progetto as progetto,
>> >   a.procedimento as procedimento,
>> >   a.geometria1 as geometria
>> > from
>> >   schemappa.repository_wfs as a
>> > where
>> >   a.geometria1 is not null;
>> >
>> > I will know if discover something.
>> > thx again.
>> >
>> > 2016-11-16 8:54 GMT+01:00 Matthias Kuhn <matthias at opengis.ch>:
>> > > Hi Andrea,
>> > >
>> > > It works here.
>> > > Do you have an instead of insert rule written on your view?
>> > > It works for me this way (but my views are too complicated for
>> postgres
>> > > to create default insert rules by default anyway).
>> > > You can also try to alter your view with a default value for the
>> column
>> > > in question.
>> > >
>> > > Best regards
>> > > Matthias
>> > >
>> > > On 11/16/2016 08:15 AM, Andrea Peri wrote:
>> > >> Hi,
>> > >> I'm having some trouble to edit a postgis view using qgis.
>> > >>
>> > >> The view is a simple:
>> > >> select list-of-field from table1;
>> > >>
>> > >> And the table1 is a table with a Primary key on a serial type field.
>> > >>
>> > >> I add the first record and all was ok. The commit too.
>> > >> When go to add the second record.
>> > >> I start to have trouble.
>> > >> The qgis report my always a constraint violation of the pk.
>> > >> I try to leave the pk field empty, or try to fill it with a value.
>> > >> No solution work.
>> > >>
>> > >> So my question is if is confirmed that qgis (I use the 2.18) version)
>> > >> is working in editing with a postgis view .
>> > >>
>> > >> Thx.
>> > >> A.
>> > >>
>> > >>
>> > >>
>> > > _______________________________________________
>> > > Qgis-user mailing list
>> > > Qgis-user at lists.osgeo.org
>> > > List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> > > Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> >
>> >
>> > _______________________________________________
>> > Qgis-user mailing list
>> > Qgis-user at lists.osgeo.org
>> > List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> > Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20161116/b1d7d9fb/attachment.html>