[GRASS-dev] Re: [GRASS-user] Views in pgSQL

[Markus Neteler]

On Wed, Apr 11, 2007 at 05:25:24PM +0200, Moritz Lennert wrote:
> On 11/04/07 13:48, Jaros??aw Jasiewicz wrote:
> >Moritz Lennert napisa??(a):
> >>On 11/04/07 13:01, Jaros??aw Jasiewicz wrote:
> >>>Hi
> >>>
> >>>Is still possible to use pgSQL views to connect vectors?
> >>>
> >>>Connecting views was possible for or five month ago (in spite of 
> >>>error messages during creating views).
> >>>
> >>>I have some vector joinred to view They still working. But now, when 
> >>>I tried to connect new vector with view I recived simple error 
> >>>message that there is no table I plan to join
> >>
> >>What is the exact error message you are receiving.
> >>
> >>
> >>>
> >>>If that possibilites was removed (after my post, unfortunatly) please 
> >>>let me know
> >>
> >>I am not aware of any related changes...
> 
> 
> Sorry, I spoke to fast. I think I now found the culprit:
> 
> revision 1.40
> date: 2006/11/28 08:42:02;  author: markus;  state: Exp;  lines: +1 -1
> if table doesn't exist: fatal error

Which file is that?
Ah, below I see that you mean v.db.connect/main.c.
 
> Before it was only a warning, not a fatal error, and so the connection 
> was established and worked.
> 
> This undoes what Radim did two years earlier:
> 
> revision 1.24
> date: 2004/11/25 13:15:47;  author: radim;  state: Exp;  lines: +2 -2
> error -> warning if table does not exist
> 
> Markus, what was the reason that you changed this ?

I don't really remember but I think that it broke scripts which
use v.db.connect.
At least: if the source code doesn't contain a comment to keep
special tricks it's likely that they get lost.

> Either we have to revert this again, or we have to find a way to check 
> for views as well as tables, which means either implementing 
> db_view_exists() (with all the individual implementations, or just 
> modify the list_tables functions of the individual drivers to include 
> views. In the PostgreSQL driver, the statement used is ( ):
> 
> "select * from pg_tables where tablename !~ 'pg_*' order by tablename"
> 
> For views this would have to be something like:
> 
> SELECT viewname FROM pg_views WHERE schemaname NOT IN 
> ('pg_catalog','information_schema') AND viewname !~ '^pg_';
> 
> So the results of these two calls would have to be combined before 
> db__driver_list_tables() returns the results.
> 
> I don't know if there are any good reasons not to just include views 
> into the list of tables...

I have no suggestion here how to solve the problem. But yes,
views should be visible somehow.

> In the meantime, you can just modify v.db.connect at line 231 from
> 
>         G_fatal_error(_("Table <%s> does not exist in database 
> <%s>"),dbtable->answer, dbdatabase->answer);
> 
> to
> 
>         G_warning(_("Table <%s> does not exist in database 
> <%s>"),dbtable->answer, dbdatabase->answer);
> 
> 
> >
> >BTW:
> >Pg Views are very important for me when I connect-reconnect vector files 
> >to different atribute table. I use (used!) views insted of original 
> >table to avoid occasional removing original data and to speed up 
> >transfer limiting data in views only to these needed for
> >(sorry, I think everybody knows it)
> 
> I completely agree with you. Being able to connect maps to views is 
> fundamental in my eyes.

Yes. But we need a reasonable implementation.
Anyone with suggestions how to implement it?

Markus