2

I have some updateable views for inserting the data. The reason is not just "security layer", inserting into some 10 tables directly would be a pain without the view with INSTEAD OF trigger. In complete production, there will be some client app doing the inserts, but it is not ready now. A customer doesn't want to wait for the app, but also doesn't want to learn SQL just to insert few rows. Inserting the data through pgAdmin would be ideal. Unfortunately, pgAdmin shows a "new row" while inspecting data only for tables, not for views.

So is it possible to enable inserting into updateable views in pgAdmin? If not, is it possible in any other PostgreSQL GUI?

It is not just for this customer - we plan to offer the database without the client app as an opensource "demo", and we would like this demo to have some value for SQL-inept potencial customers too.

  • 1
    sql-workbench.net also allows editing a result set based on a view (of course only if the view itself is updateable as well) – a_horse_with_no_name Jul 2 '14 at 14:11
  • @a_horse_with_no_name: after brief inspection of sql-workbench, I don't think it is a good workaround. It seems easier to teach my users to write few INSERTs in pgAdmin than to teach them how to get to view the data in SQL-workbench. But thanks for a tip. – Pavel V. Jul 3 '14 at 14:44
  • Did you check the DbExplorer's "Data" tab? You can even start the DbExplorer as a stand-alone window. So the users won't see a SQL editor or something similar. Only the list of tables/views. Click on the view, click on "Data", done. – a_horse_with_no_name Jul 3 '14 at 22:19
  • @a_horse_with_no_name: I have inspected SQL Workbench and found a serious problem with statement delimiters. Unless I find some clever hack to make my DDL code applicable both within and without SQL Workbench, I must either maintain two different versions of my code or use pgAdmin for DDL and SQL Workbench to insert/update the data. It's a pain, since the GUI is very different. I can imagine why the Data tab might seem quite handy for some, but it's organized differently from pgAdmin, which makes learning hard. – Pavel V. Sep 11 '14 at 8:14
1

Sorry, no luck with pgAdmin. Per documentation:

Note that views can’t be edited and are read only by design; updatable views (using rules) are not supported at this time.

There is an item in the project's TODO list:

  • Updateable View wizard

Seems like nobody tackled that, yet. (Contributors welcome.)
There have been requests to introduce this feature in the past. You could email pgadmin-support@postgresql.org to document popular demand ...

For now, it seems like Navicat supports it.

  • I have sent a mail to pgAdmin team asking about state of updateable view wizard. Guillaume Lelarge answered that no one works on it now. – Pavel V. Aug 21 '14 at 6:21

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.