I am using PostgreSQL for GIS purposes with PostGIS and QGIS, but I think I might find more informations here than on gis.stackexchange.com since my question is not directly GIS related.
I am using views to diplay datas at will for the specific needs of my users, like that they have access to the datas as they are in the DB, but with just what they need. I added some rules to my views to make them "updatable" in QGIS and make them directly "workable" by the users.
Display in QGIS is based on attributes, but since there is the possibility that different persons will access the same data at the same moment, and they might want to display and hide some of these datas according to their needs (map printing for once). So I am looking for a way to give possibility to have a specific display for each view, and I thought about simply adding a "virtual" column in the view definition, with for example a boolean like such:
CREATE VIEW view1 AS
SELECT oid, column1, True as display from table1;
But I would like my users to be able to change the value of this column, to simply make appear or disappear the objects from the canvas (with a ruled-base styling considering this parameter). And obviously it doesn't work direclty since the update would be in conflict with the definition of the view.
Does anyone have any idea on how to achieve that? Maybe a materialized view (but I quite like the dynamics of the regular view)?
Thanks.
If the unique ID field is read from the table (i.e. it is not dynamically created via the row_number() trick commonly used with spatial views in QGIS), you could create a visibility manager table and use it in the view. You could have one table by view or one for all of them. Something similar to:
create table visibility_manager (oid bigint, visibility_status boolean, viewname text);
CREATE VIEW view1 AS
SELECT table1.oid, column1, coalesce(visibility_status,true) as display
from table1
left outer join visibility_manager
on (table1.oid = visibility_manager.oid and visibility_manager.viewname = 'view1');
see it in action: http://rextester.com/OZPN1777