Subscribe to this thread
Home - General / All posts - Postgresql View Feature Info
472 post(s)
#09-Sep-21 19:33

I'm not sure if there have been additions to 9 in the last 3.5 years, or new techniques that may permit selection in Drawings from postgresql views. At present, I am still running a sql script to DROP/CREATE/INSERT from my postgresql database into local components.

I'm not sure how QGIS does it, but they have achieved the ability to interactively "select" layer features from postgresql database views, while retaining the dataset's update-on-pan/zoom experience with that layer.

I never sent in in as a suggestion, and it may be the case that very few of your customers need the ability to interactively "select" or info-tool features in postgresql view Drawing components, but it would make my life easier if there was a way to achieve this.


6,713 post(s)
#10-Sep-21 03:26

I'm not sure if there have been additions to 9

Have you tried what you want to do using the current build?

472 post(s)
#10-Sep-21 05:38

I have, yes.


9,738 post(s)
#12-Sep-21 11:51

We'll re-check PostgreSQL views. I remember the original topic, we did have some improvements, but they were not applicable to PostgreSQL specifically.


9,738 post(s)
#13-Sep-21 16:46

OK, we checked. We added something that should work for all databases some time ago.

To recap, the issue was that, yes, you could use a view to return a table, and, yes, you could create a drawing based on that view, but then you could not select or Alt-click records, even if the view contained a unique field, because we could not understand that the view contained a unique field, because the database is not telling us that kind of info. So, we added means to let the user tell us which field is unique.

Do the following: connect to the database in 9, open the view (this will show its code), run it (this will run it and show the result table). The table should be uneditable (gray) because we have no idea that it contains a unique field and we can use that field to edit it. In the table, right-click the header for the unique field and select 'Use as identity'. This should make the table editable (white). If you now open the drawing tied to the view, you should be able to select / Alt-click records. The choice will also persist between sessions.

The only caveat is: if you make a mistake and tell us to use the field with non-unique values as identity, we will sometimes detect that the field does not really have unique values and sometimes we will not be able to detect that. If we detect it, we will turn the identity flag off, no worries. But if we do not, editing a record might edit multiple records, and deleting a record might delete multiple records.

472 post(s)
#13-Sep-21 17:20

Very helpful, confirmed that works with my data, thank you. That "Use as Identity" rings a bell, I just couldn't seem to google my way to it using other terms.

For others, it was announced back in 2018

And it's in the docs here

Again, much appreciated.

472 post(s)
#15-Sep-21 17:02

Alright, so, selection and alt-click work as advertised, no complaints there.

As the exposed component remains a query, rather than a table, even after specifying an identity column, there is no synchronisation between selection of rows in the "table" (query results) window, and the features on the map. The same is true for materialized views in postgresql; the component is exposed as a query in 9, rather than a table. This means no ability to ctrl-click on the layer handle in a map window to "zoom to selection".

So, maybe I'm nitpicking, yes. "Would be great to have that ability", though. Thanks again.


9,738 post(s)
#16-Sep-21 10:40

Well, you are not nitpicking, no. It's just a fundamental question of what should be a table vs what should be a query. Or perhaps how should we treat a table returned by a query when it is used as a base of a component.

Here's the current logic:

If a table is static, or, more specifically, if only one instance of its records exists (in the case of an external database, on a server), then we treat that table accordingly. That is, if you view that table as a drawing in some window and select some records, that selection persists. If you then close the window and reopen it, the records will still be selected. If you open a different window showing the records of that table in some other way, some of those records will be selected there.

If a table may have multiple instances of records, eg, because it is dynamic = computed on the fly, then we treat it accordingly as well. If you view that table, we create an instance of its records (instance 1). If you then view that table in a different window, we create a new instance of its records (instance 2). We assume that the records in different instances might be different as well. In the case of a query that might be because the text of the query has changed, or because the data in the queried tables has changed, or because nothing has changed but the text of the query includes things like "let's look at all records for the last 2 hours" and the current time has changed. (As an aside, we agree that in the case of materialized views, records are actually stored on the server and there is only one instance of them. But a materialized view is still closer to a view than it is to a table, because it still has the text that can be changed relatively easily, so we treat it as a view.)

In general, we can limit tables returned by a query to a single instance. Maybe that's a better approach. If you change the text of the query, you would still be able to recompute the table using View - Refresh in the table window, etc. If you make changes to the underlying tables (eg, add some new records) and want to recompute the query after, same thing, manual refresh whenever you want. If the text of the query depends on the current date or generates random values, same again, manual refresh. Then we can share the selection. Do you think that would be better?

Mike Pelletier

1,906 post(s)
#16-Sep-21 16:33

I do, at least from how I understand it. A manual refresh seems well worth the additional functions. Maybe place a refresh button easily accessed with the table to help indicate that the table needs refreshing to be current.

On a related note, ESRI has something called a definition query option for a layer. It allows applying a simple SQL statement to remove certain records from what gets displayed on the map. We can do that too with a separate query and related drawing. Although in trying it now, (query: select * from drawing, create drawing2 from query), the drawing2 has no records. Not sure why this fails.

Nevertheless, this approach adds two more components to the project and it is a fairly common desire. Perhaps it is worth adding a definition query equivalent 9? Maybe this could be somehow another way to approach the issues discussed above?

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.