I don't know if it's the right place, sorry if it's not, Postgresql starting from 9.1 supports "instead of" triggers which -among other things- used for updatable views Postgresql starting from 9.3 makes all simple views automatically updatable I have tested this with ODBC and Access and it worked, the only modification I needed was to add "oid" field to the view (I guess it services as a primary key, not sure how good of an idea it is) while if I used SDBC and Base, all views are read-only, it would be great if we can have updatable views in Libreoffice, my use case is row-level-security, I'm building a huge and complicated application, the only thing missing is row-level-security and if I have that I can get away without building a custom web application and only using Libre office at least can somebody give me guidance on which file in the source code I need to look at so I can try to implementing that my self thanks a lot for you hard work guys
(In reply to comment #0) > while if I used SDBC and Base, all views are read-only, > it would be great if we can have updatable views in Libreoffice, > my use case is row-level-security, (...) Views don't offer row-level security if the users can define their own server-side (SQL) functions, because the optimiser can decide to call them on rows outside of the view (if the view condition is "more expensive"). Ah, but I now discover that newer PostgreSQL have a security_barrierflag for that... Oh, nice. > at least can somebody give me guidance on which file in the source code I > need to look at so I can try to implementing that my self Since you intend to work on it yourself, I assign this enhancement to you. I'll prepare you a few code pointers, but probably not today.
First, you need to find out why these views read-only. Are they read-only also when using LibreOffice and ODBC, or LibreOffice and JDBC? Maybe LibreOffice does not find a primary key. Since you had to add the "oid" column to make it work with other systems, possibly LibreOffice does not see the "oid" column as a primary key? The code that marks tables without a primary key as read-only is the constructor dbaccess::ORowSetCache::ORowSetCache in file "dbaccess/source/core/api/RowSetCache.cxx"; see the places where it manipulates m_nPrivileges. The gist of the "find primary columns" code is dbtools::getPrimaryKeyColumns_throw in file "connectivity/source/commontools/dbtools.cxx". It basically uses the driver's getKeys() and then looks for a key that the driver has flagged as PRIMARY. In the case of PostgreSQL-SDBC, the object returned by xKeys->getByIndex(i) should be a pq_sdbc_driver::Key, from file connectivity/source/drivers/postgresql/pq_xkey.cxx. The properties supported by that are defined in pq_statics.cxx (look for the line statics.refl.key.pProps = ... ) However, the properties of a Key are filled in in Keys::refresh (file pq_xkeys.cxx). This takes it by reading from pg_constraint. I guess the driver will have to "pierce" the view veil and: 1) Check that the view is "simple" view. 2) Execute the query filling in the underlying *table* name, not the *view* name. 3) Translate column names (in case they are renamed in the view) Unless PostgreSQL has another pg_FOO table (or something in INFORMATION_SCHEMA) that will give the information more easily.
I've already tried JDBC, it's not read-only when I add oid, but still it doesn't work because it's too buggy, I can add rows (using instead of rules\triggers), but I can't update rows, it crashes I will start working on the rest, thanks a lot for your pointers
Ali? Any progress? Do you still intend to work on that?
No feedback since years, let's put it to NEW and unassign. Ali: if you want to get back to it, don't hesitate to assign yourself again.
I've tested that if one hacks PostgreSQL to add a primary key on the view (by direct inserts in pg_constraint) and the default values (by direct inserts in pg_attrdef) on the columns of the view, LibreOffice considers the view as an updatable table and it works correctly.