Re: DROP/CREATE - Mailing list pgadmin-hackers

>I don't think rules are an issue are they? Can you create them on Views
>(certainly pgAdmin won't let you - should it?) - scrub that, (typing as I
>think!) how else would you create an updateable view using rules? Does the
>same apply to triggers i.e. can you create them on views?
Yes you can. This is a great feature and the only way for updating views.


> > Another issue is that views get very complex when commited.
> > An example
> > would be:
> > CREATE VIEW "view_data_source"
> > AS SELECT * FROM table 1
> > LEFT JOIN table 2 ON (xx=ccc)
> > LEFT JOIN table 3 ON (xx=ccc)
> >
> > When committed, this view becomes a nightmare because it can
> > hardly be
> > read. Another subsequent problem is that views with SELECT *
> > FROM table1
> > need updating when fields are added/dropped in tables. In the
> > end we always
> > come up with the conclusion that changes should be applied
> > internally to
> > PostgreSQL.
>
>I'm beginning to think this is correct. I see the work you did in pgAdmin I
>as a kind of proof of concept. The more we discuss these things, the more I
>think of problems like this that would be seriously hard work to do client
>side. To get around the problem here for example, you need to have a full
>blown parser to figure out the tables involved. What if the view calls some
>functions as well? What if that function takes an entire tuple from a
>(modified) table as an argument (or returns it) - then things get really
>hairy.
>
>I think the only way we can reliably do this is with the addition of either
>safe CREATE OR REPLACE sql commands, or addition of a suitable
>pg_dependencies table which is maintained by PostgreSQL itself.

A third solution would be to work with PL/pgSQL and development tables (i.e
code repository).
The notion of Code repository is interesting because it is not linked to
PostgreSQL internals.
A code repository can be located anywhere on the planet. Cool feature for
development teams.

With PL/pgSQL we can ***easily*** track and rebuild objects. Before that,
we need a PL/pgSQL wizard in pgAdmin.
PostgreSQL might incorporate PL/pgSQL as a standard feature when protection
for infinite loops is added.

Code repositories would be a nice solution as completely independent from
PgAdmin. This means PhpPgAdmin would also benefit from it. Ultimately, when
Postgresql gets PL/pgSQL infinite loop protection, repositories could get
included in Postgresql. So why not go for it?

> > I am going to have a look at updating views within a single
> > transaction.
> > Are there special guidelines for compiling phSchema?
>
>No, just that if you break compatibility you may need to run buildall.bat(?)
>to recompile everything. Please don't commit anything to do with this until
>I've taken a look either - I don't want to add any more features now until
>after the first full release.

OK, I will not upload pgSchema to CVS if modified. On my side, I have to
consider migration from pgAdmin I to pgAdmin II to comply with PostgreSQL
7.2. Without rebuilding, I cannot work and maintain 100 tables, 50 views,
30 triggers and 200 functions.

What are your plans? If you don't mind, I would prefer to go for a PL/pgSQL
repository feature. This would be more advanced that in pgAdmin I, testing
the new features on my side only. Please advise me for pgShema compilation
guidelines.

Cheers,
Jean-Michel