Обсуждение: POSS. FEATURE REQ: "Dynamic" Views

For lack of a better term, but I was curious if there
is/was any thought about making PG's views
automatically "see" changes in underlying tables, as
opposed to currently having to drop/create all
corresponding views if a table's structure (add/delete
fields, etc.) is changed.

CSN



____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs

On Thu, Aug 25, 2005 at 03:03:30PM -0700, CSN wrote:
> For lack of a better term, but I was curious if there
> is/was any thought about making PG's views
> automatically "see" changes in underlying tables, as
> opposed to currently having to drop/create all
> corresponding views if a table's structure (add/delete
> fields, etc.) is changed.

There's not currently a TODO for this, no.
http://www.postgresql.org/docs/faqs.TODO.html I'm not sure how hard it
would be to do, since currently CREATE VIEW v AS SELECT * FROM t
actually expands the * out at creation time.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461

Brand X simulates this in their GUI diagrammer by tracking
dependencies and dropping and  recreating dependent views on schema
changes.  This might be a better job for one of the GUI tools for us
too, rather than trying to put it in the back end.  Brand X doesn't do
it in their backend either.

On 8/25/05, Jim C. Nasby <jnasby@pervasive.com> wrote:
> On Thu, Aug 25, 2005 at 03:03:30PM -0700, CSN wrote:
> > For lack of a better term, but I was curious if there
> > is/was any thought about making PG's views
> > automatically "see" changes in underlying tables, as
> > opposed to currently having to drop/create all
> > corresponding views if a table's structure (add/delete
> > fields, etc.) is changed.
>
> There's not currently a TODO for this, no.
> http://www.postgresql.org/docs/faqs.TODO.html I'm not sure how hard it
> would be to do, since currently CREATE VIEW v AS SELECT * FROM t
> actually expands the * out at creation time.
> --
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> Pervasive Software        http://pervasive.com        512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> ... since currently CREATE VIEW v AS SELECT * FROM t
> actually expands the * out at creation time.

I believe that that behavior is required by the SQL spec.  For instance,
SQL92's description of ALTER TABLE ADD COLUMN sez:

            Note: The addition of a column to a table has no effect on any
            existing <query expression> included in a view descriptor or
            <search condition> included in constraint descriptor because
            any implicit <column reference>s in these clauses are replaced
            by explicit <column reference>s when the clause is originally
            evaluated. See the Syntax Rules of Subclause 7.10, "<query ex-
            pression>".

            regards, tom lane

Ian Harding <harding.ian@gmail.com> writes:

> Brand X doesn't do it in their backend either.

If your Brand X is the same as my Brand X then it's worth noting that they
didn't previously do anything sane in their backend. It used to invalidate all
your views and you had to recompile them before they would work again.

I wonder whether it would be saleable to have an option to work around this
"feature". I'm thinking one of two directions:

1) An alternate type of view that just stores the text of the view and is
interpreted at time of use like:

CREATE DYNAMIC VIEW foo AS (SELECT * FROM tab)

or 2) A command to recompile a view which would go back to the original source
and reinterpret it like:

ALTER VIEW foo RECOMPILE

Or I guess you could have the latter and then add the former as a view that
automatically recompiles any time a object it depends on is altered.

--
greg

On Fri, Aug 26, 2005 at 04:54:06PM -0400, Greg Stark wrote:
>
> Ian Harding <harding.ian@gmail.com> writes:
>
> > Brand X doesn't do it in their backend either.
>
> If your Brand X is the same as my Brand X then it's worth noting that they
> didn't previously do anything sane in their backend. It used to invalidate all
> your views and you had to recompile them before they would work again.
>
> I wonder whether it would be saleable to have an option to work around this
> "feature". I'm thinking one of two directions:
>
> 1) An alternate type of view that just stores the text of the view and is
> interpreted at time of use like:
>
> CREATE DYNAMIC VIEW foo AS (SELECT * FROM tab)
>
> or 2) A command to recompile a view which would go back to the original source
> and reinterpret it like:
>
> ALTER VIEW foo RECOMPILE
>
> Or I guess you could have the latter and then add the former as a view that
> automatically recompiles any time a object it depends on is altered.

I agree that CREATE DYNAMIC would be a good thing to have. It would
certainly save me time on some of my projects.

Can we TODO this?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461

Jim C. Nasby wrote:
> > I wonder whether it would be saleable to have an option to work around this
> > "feature". I'm thinking one of two directions:
> >
> > 1) An alternate type of view that just stores the text of the view and is
> > interpreted at time of use like:
> >
> > CREATE DYNAMIC VIEW foo AS (SELECT * FROM tab)
> >
> > or 2) A command to recompile a view which would go back to the original source
> > and reinterpret it like:
> >
> > ALTER VIEW foo RECOMPILE
> >
> > Or I guess you could have the latter and then add the former as a view that
> > automatically recompiles any time a object it depends on is altered.
>
> I agree that CREATE DYNAMIC would be a good thing to have. It would
> certainly save me time on some of my projects.
>
> Can we TODO this?

How is this different from materialized views, which is already on the
TODO list?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Sat, Aug 27, 2005 at 12:50:44AM -0400, Bruce Momjian wrote:
> Jim C. Nasby wrote:
> > > I wonder whether it would be saleable to have an option to work around this
> > > "feature". I'm thinking one of two directions:
> > >
> > > 1) An alternate type of view that just stores the text of the view and is
> > > interpreted at time of use like:
> > >
> > > CREATE DYNAMIC VIEW foo AS (SELECT * FROM tab)
> > >
> > > or 2) A command to recompile a view which would go back to the original source
> > > and reinterpret it like:
> > >
> > > ALTER VIEW foo RECOMPILE
> > >
> > > Or I guess you could have the latter and then add the former as a view that
> > > automatically recompiles any time a object it depends on is altered.
> >
> > I agree that CREATE DYNAMIC would be a good thing to have. It would
> > certainly save me time on some of my projects.
> >
> > Can we TODO this?
>
> How is this different from materialized views, which is already on the
> TODO list?

The idea behind the DYNAMIC VIEW is that if you made a DDL change in the
table it could be reflected in the view. So for example, if you defined
a view as SELECT * FROM table; and then added a field to the table that
field would also show up in the view.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461

Bruce Momjian <pgman@candle.pha.pa.us> writes:

> How is this different from materialized views, which is already on the
> TODO list?

It's entirely unrelated.

Materialized views are about having all the data stored in advance. They're
really tables that have some sort of process to keep the data in them in sync
with other tables according to a view definition.

These "dynamic views" are really just normal views operationally. The only
difference is what happens to them when DDL changes are made to the objects
they depend on.

In normal SQL standard views column references are resolved at creation time
and continue to point to the same physical column despite column renames. And
"select *" doesn't change when new columns are added.

What these users and myself would prefer is something that remembers the
original view definition text and reinterprets it according to the new
definition of the underlying tables. So if I swap two columns by renaming them
I could recompile the view and it would swap which columns were used where.
And if I add new columns "select *" would include the new columns.

I'm starting to be a little skeptical about "CREATE DYNAMIC VIEW". I think
what would be better to proceed conservatively and just add a "ALTER VIEW
RECOMPILE". That at least gives the user a way to easily recover the original
intention without having to reenter the view definition manually.

It would also be useful to have a warning when any DDL is done to a column
being used in a view or adding a new column in any table where a view on the
table had a "select *". That would be useful independently of any automagic
recompile feature. Even if the user has to go fetch the original view
definition from his DDL file (which hopefully he saved) the warning will at
least make it more likely he'll remember to do so.


IF you find there's support for these ideas from the powers that be then the
TODOs would look something like:

o Add support for ALTER VIEW RECOMPILE which would recreate a view definition
  using the original SQL DDL definition that originally created it.

o Add warning whenever DDL to a table affects a view dependent on that table.
  Such as when a column is altered that is referenced in the view or when a
  column is added if a "select *" appears in the view.

o Add some option to CREATE VIEW to cause the above ALTER VIEW RECOMPILE to
  automatically happen whenever DDL to a table affects the view.

I think the first of these two are no-brainers if they're implemented well.
The third seems less likely to garner immediate support.

--
greg

Well, I just added to TODO:

    * Allow VIEW/RULE recompilation when the underlying tables change

Is dynamic view a industry-standard name?  If so, I will add it to the
TODO.


Updated TODO is:

    * Allow VIEW/RULE recompilation when the underlying tables change

      Another issue is whether underlying table changes should be reflected
      in the view, e.g. should SELECT * show additional columns if they
      are added after the view is created.

---------------------------------------------------------------------------

Greg Stark wrote:
>
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
> > How is this different from materialized views, which is already on the
> > TODO list?
>
> It's entirely unrelated.
>
> Materialized views are about having all the data stored in advance. They're
> really tables that have some sort of process to keep the data in them in sync
> with other tables according to a view definition.
>
> These "dynamic views" are really just normal views operationally. The only
> difference is what happens to them when DDL changes are made to the objects
> they depend on.
>
> In normal SQL standard views column references are resolved at creation time
> and continue to point to the same physical column despite column renames. And
> "select *" doesn't change when new columns are added.
>
> What these users and myself would prefer is something that remembers the
> original view definition text and reinterprets it according to the new
> definition of the underlying tables. So if I swap two columns by renaming them
> I could recompile the view and it would swap which columns were used where.
> And if I add new columns "select *" would include the new columns.
>
> I'm starting to be a little skeptical about "CREATE DYNAMIC VIEW". I think
> what would be better to proceed conservatively and just add a "ALTER VIEW
> RECOMPILE". That at least gives the user a way to easily recover the original
> intention without having to reenter the view definition manually.
>
> It would also be useful to have a warning when any DDL is done to a column
> being used in a view or adding a new column in any table where a view on the
> table had a "select *". That would be useful independently of any automagic
> recompile feature. Even if the user has to go fetch the original view
> definition from his DDL file (which hopefully he saved) the warning will at
> least make it more likely he'll remember to do so.
>
>
> IF you find there's support for these ideas from the powers that be then the
> TODOs would look something like:
>
> o Add support for ALTER VIEW RECOMPILE which would recreate a view definition
>   using the original SQL DDL definition that originally created it.
>
> o Add warning whenever DDL to a table affects a view dependent on that table.
>   Such as when a column is altered that is referenced in the view or when a
>   column is added if a "select *" appears in the view.
>
> o Add some option to CREATE VIEW to cause the above ALTER VIEW RECOMPILE to
>   automatically happen whenever DDL to a table affects the view.
>
> I think the first of these two are no-brainers if they're implemented well.
> The third seems less likely to garner immediate support.
>
> --
> greg
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

"Jim C. Nasby" <jnasby@pervasive.com> writes:
>> How is this different from materialized views, which is already on the
>> TODO list?

> The idea behind the DYNAMIC VIEW is that if you made a DDL change in the
> table it could be reflected in the view. So for example, if you defined
> a view as SELECT * FROM table; and then added a field to the table that
> field would also show up in the view.

But why exactly is this a good idea?  It seems like an absolutely
horrible idea to me.  It is oft-repeated advice that you don't use
"SELECT *" ever in production programming, because your applications
will break as soon as any columns are added (or removed, even if they
don't make any use of those columns).  The proposed dynamic view
facility would move that instability of results right into the views.

What's more, I cannot see any benefit to be gained over just issuing
the expanded query directly.  You couldn't layer a normal view over
a dynamic view (not having any idea what columns it'll return), nor
even a prepared statement, because those things nail down specific
result columns too.  So it's just an awkwardly expressed form of
query macro that can only be used in interactively-issued commands.

I think the burden of proof is on the proponents of this idea to show
that it's sensible, and it doesn't deserve to be in TODO just because
one or two people think it'd be nice.

            regards, tom lane

Tom Lane wrote:

>
>
>But why exactly is this a good idea?
>
Especially since it seems one could write PLPGSQL functions to emulate
this if it was so important.  The lack of PLPGSQL functions to do this
seems to indicate that it is not worth the trouble,

>It is oft-repeated advice that you don't use
>"SELECT *" ever in production programming, because your applications
>will break as soon as any columns are added (or removed, even if they
>don't make any use of those columns).
>
With all due respect, I often use SELECT * in production programming
becuase the languages I write in allow me to return the results as a
hash table.  In this case, things only break if you delete a needed
column, and added columns have no real effect unless they are of
substantial size (but this is a separate issue).  With a good RAD
environment (such as Python, Perl, or PHP), I don't think that SELECT *
is unduly dangerous.

>  The proposed dynamic view
>facility would move that instability of results right into the views.
>
>
Again, this would not be *that* hard to do with PLPGSQL and a function
like redefine_view(name, text) which would store the definitions of the
views in a temporary table, and then rebuild dependant views.  Again the
fact that it is not commonly done indicates simply that there is no need.

Best Wishes,
Chris Travers
Metatron Technology Consulting

Bruce Momjian <pgman@candle.pha.pa.us> writes:

> Well, I just added to TODO:
>
>     * Allow VIEW/RULE recompilation when the underlying tables change
>
> Is dynamic view a industry-standard name?  If so, I will add it to the
> TODO.

"DYNAMIC" is something I made up.

"ALTER VIEW RECOMPILE" is Oraclese but I'm not sure what we're talking about
here is exactly the same purpose. I'm not sure it even does anything in Oracle
any more. It used to be that *any* DDL on underlying tables caused view on
them to become invalid and produce errors until they were recompiled. I think
that's changed and "recompile" may be a noop now on Oracle.

>
> Updated TODO is:
>
>     * Allow VIEW/RULE recompilation when the underlying tables change
>
>       Another issue is whether underlying table changes should be reflected
>       in the view, e.g. should SELECT * show additional columns if they
>       are added after the view is created.

I think we're 100% certain that it should not do this by default. The spec
requires it. What's unknown is whether there should be an option to do it
automatically. In my humble opinion there's no downside to having a facility
for users to do it manually though.

Hence why I separated it into three points:

> > o Add warning whenever DDL to a table affects a view dependent on that table.
> >   Such as when a column is altered that is referenced in the view or when a
> >   column is added if a "select *" appears in the view.
> >
> > o Add some option to CREATE VIEW to cause the above ALTER VIEW RECOMPILE to
> >   automatically happen whenever DDL to a table affects the view.
> >
> > I think the first of these two are no-brainers if they're implemented well.
> > The third seems less likely to garner immediate support.

--
greg

Tom Lane <tgl@sss.pgh.pa.us> writes:

> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> >> How is this different from materialized views, which is already on the
> >> TODO list?
>
> > The idea behind the DYNAMIC VIEW is that if you made a DDL change in the
> > table it could be reflected in the view. So for example, if you defined
> > a view as SELECT * FROM table; and then added a field to the table that
> > field would also show up in the view.
>
> But why exactly is this a good idea?  It seems like an absolutely
> horrible idea to me.  It is oft-repeated advice that you don't use
> "SELECT *" ever in production programming, because your applications
> will break as soon as any columns are added (or removed, even if they
> don't make any use of those columns).  The proposed dynamic view
> facility would move that instability of results right into the views.

Just because something is oft-repeated doesn't make it good advice. I am
convinced that advice originates in the fact that many databases handled
"select *" very poorly. These other databases often had limitations like
having it produce errors or even incorrect results if the underlying table was
changed.

From a programming aesthetics point of view it's downright important to use
it. Not using it forces the programmer to distribute knowledge about columns
and how they will be used throughout many more layers of programming than
otherwise necessary. If

Far from breaking as soon as columns are added or removed, the use of select *
insulates the application from the changes. I can add a column to my front-end
templates without having to modify every layer below it. Or can add a column
to a database and use it immediately in the front-end without modifying every
layer in between.

> What's more, I cannot see any benefit to be gained over just issuing
> the expanded query directly.  You couldn't layer a normal view over
> a dynamic view (not having any idea what columns it'll return), nor
> even a prepared statement, because those things nail down specific
> result columns too.  So it's just an awkwardly expressed form of
> query macro that can only be used in interactively-issued commands.

I think we have two different ideas of what we're talking about. I'm talking
about absolutely normal views. They can be used in the same ways and behave
the same as normal views.

I'm just suggesting adding a command that would do exactly the same thing as
having the user issue a "CREATE OR REPLACE VIEW" with the exact same
definition text as originally used.

The point here is to give a user an out who would otherwise be completely
stuck. If he didn't save the original view definition text he has to now
reverse engineer what was intended from the reconstructed view definition that
pg_dump gives which isn't always obvious.

> I think the burden of proof is on the proponents of this idea to show
> that it's sensible, and it doesn't deserve to be in TODO just because
> one or two people think it'd be nice.

I think, given the confusion shown by myself and this other user, that the
evidence is there that the spec behaviour violates the principle of least
surprise and warrants warnings. I think just about any time these warnings
would be fire there's a better than 50% chance the programmer is about to be
bitten by a nasty surprise.

The "alter view recompile" is the thing one or two people think would be nice.
I can offer a use case for "alter view recompile" that might be somewhat more
convincing than hand waving:

Consider the case of someone who has a large growing table with log records.
He wants to periodically rotate it out and start a fresh table. Much like what
logrotate does for files. Now any view on that table will follow the renamed
table instead of using the fresh new table. If the user doesn't keep around a
complete DDL definition for the table he can't even fix the problem robustly.
He has to try to reconstruct all the views and hopefully get their definitions
right.

In fact the situation is quite similar to the situation with daemons that
don't reopen their log files regularly. In those cases however those daemons
invariably support reopening their log files on some even like kill -HUP. This
is precisely because restarting the daemon is intrusive and error prone, just
as having to reconstruct the view definitions from scratch would be.

--
greg

Greg Stark wrote:

>Tom Lane <tgl@sss.pgh.pa.us> writes:
>
>
>
>>"Jim C. Nasby" <jnasby@pervasive.com> writes:
>>
>>
>>>>How is this different from materialized views, which is already on the
>>>>TODO list?
>>>>
>>>>
>>>The idea behind the DYNAMIC VIEW is that if you made a DDL change in the
>>>table it could be reflected in the view. So for example, if you defined
>>>a view as SELECT * FROM table; and then added a field to the table that
>>>field would also show up in the view.
>>>
>>>
>>But why exactly is this a good idea?  It seems like an absolutely
>>horrible idea to me.  It is oft-repeated advice that you don't use
>>"SELECT *" ever in production programming, because your applications
>>will break as soon as any columns are added (or removed, even if they
>>don't make any use of those columns).  The proposed dynamic view
>>facility would move that instability of results right into the views.
>>
>>
>
>Just because something is oft-repeated doesn't make it good advice. I am
>convinced that advice originates in the fact that many databases handled
>"select *" very poorly. These other databases often had limitations like
>having it produce errors or even incorrect results if the underlying table was
>changed.
>
>
So it seems to be an implimentation, not a data problem.  I will also
state that some applications go off numeric column indexes which cause
problems if a column is deleted.  Imagine
"SELECT * FROM customers ORDER BY 2;"

Drop the first column and replace it with another column at the end of
the table and suddenly the ordering changes....  Similarly if you go off
column numbers, suddenly your data types are off and your application
doesn't know what to do with the data.

This is why this is OK if you get it as a hash table, but not if you get
it as a simple array.

>From a programming aesthetics point of view it's downright important to use
>it. Not using it forces the programmer to distribute knowledge about columns
>and how they will be used throughout many more layers of programming than
>otherwise necessary. If
>
>Far from breaking as soon as columns are added or removed, the use of select *
>insulates the application from the changes. I can add a column to my front-end
>templates without having to modify every layer below it. Or can add a column
>to a database and use it immediately in the front-end without modifying every
>layer in between.
>
>

Well said.

Now, let me give you an example.....

I created a view for a retail management application I maintain for
inventory activity on a daily basis.  I then created another view  to
compile these into quarterly reports.

The workaround is to keep the views on a separate .sql file and replay
them agains thte database when you want to change something on an
underlying view.

I don't know.  On this matter I am fairly undecided.  I think it would
be useful to have it, but it is a really minor enhancement I think, and
I would rather see the core developers focus on other more pressing matters.

IMO, this is not a bad idea.  It just is not that necessary at the
moment.  Especially since such a framework could be written pretty
easily in plpgsql.  Indeed I would rather see a prototype in plgsql than
in the backend at the moment.  But this is just me.

>
>I think we have two different ideas of what we're talking about. I'm talking
>about absolutely normal views. They can be used in the same ways and behave
>the same as normal views.
>
>I'm just suggesting adding a command that would do exactly the same thing as
>having the user issue a "CREATE OR REPLACE VIEW" with the exact same
>definition text as originally used.
>
>
>
So, why not write a plgsql function that does as follows:

CREATE FUNCTION define_view(name, text) returns BOOL AS '
DECLARE
  vname ALIAS FOR $1;
  vdef ALIAS FOR $2;
BEGIN
  INSERT INTO def_view (view_name, view_definition) values (vname, vdef);
  EXECUTE ''CREATE OR REPLACE VIEW ''||vname||'' AS ''||vdef;
  RETURN TRUE;
END;
' LANGUAGE PLPGSQL;

CREATE FUNCTION recompile_view (name) RETURNS BOOL AS '
DECLARE
  vname ALIAS FOR $1;
  vdef TEXT;
BEGIN
  SELECT INTO vdef view_definition FROM def_view WHERE view_name = vname;
  EXECUTE ''CREATE OR REPLACE VIEW ''||vname||'' AS ''||vdef;
  RETURN TRUE;
END;
' LANGUAGE PLPGSQL;

You might have to extend this to track and maintain rules for inserting
and updating on the view.....

Best Wishes,
Chris Travers
Metatron Technology Consulting

My first idea when this was mentioned was more like

ALTER TABLE .... CASCADE

where CASCADE meant recompile all the views that depend on that table.

Not that I think any of this is a good idea, but if it was going to be
done, that's what would make the most sense to me.


- Ian

Greg Stark wrote:

>Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
>
>
>>Well, I just added to TODO:
>>
>>    * Allow VIEW/RULE recompilation when the underlying tables change
>>
>>Is dynamic view a industry-standard name?  If so, I will add it to the
>>TODO.
>>
>>
>
>"DYNAMIC" is something I made up.
>
>"ALTER VIEW RECOMPILE" is Oraclese but I'm not sure what we're talking about
>here is exactly the same purpose. I'm not sure it even does anything in Oracle
>any more. It used to be that *any* DDL on underlying tables caused view on
>them to become invalid and produce errors until they were recompiled. I think
>that's changed and "recompile" may be a noop now on Oracle.
>
>
It's still necessary in Oracle 9i.  Any time a table is changed that has
a view on it Bad Things Happen.

Jeff

On Sat, 2005-08-27 at 09:48, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> >> How is this different from materialized views, which is already on the
> >> TODO list?
>
> > The idea behind the DYNAMIC VIEW is that if you made a DDL change in the
> > table it could be reflected in the view. So for example, if you defined
> > a view as SELECT * FROM table; and then added a field to the table that
> > field would also show up in the view.
>
> But why exactly is this a good idea?  It seems like an absolutely
> horrible idea to me.  It is oft-repeated advice that you don't use
> "SELECT *" ever in production programming, because your applications
> will break as soon as any columns are added (or removed, even if they
> don't make any use of those columns).  The proposed dynamic view
> facility would move that instability of results right into the views.
>
> What's more, I cannot see any benefit to be gained over just issuing
> the expanded query directly.  You couldn't layer a normal view over
> a dynamic view (not having any idea what columns it'll return), nor
> even a prepared statement, because those things nail down specific
> result columns too.  So it's just an awkwardly expressed form of
> query macro that can only be used in interactively-issued commands.
>
> I think the burden of proof is on the proponents of this idea to show
> that it's sensible, and it doesn't deserve to be in TODO just because
> one or two people think it'd be nice.

Actually, I've written a few very abstracted database applications that
basically did a select * and then used the libpq stuff to find the
column names and types and such and put the data on the screen in an
edit form.  Such applications know NOTHING about the actual structure of
the table or view they are operating on, and rely on getting said data
from the database.

This makes them very portable.  Need another instance of such an app and
all you need to do is copy in the files and edit one or two config lines
to tell it which table(s) to hit and you're gold.

So, there are certain types of applications where select * is pretty
useful.  I'm not saying I like the idea of dynamic views, but I can see
in a few circumstances where they might be useful.  I can see far more
where they can cause headaches galore.

Just pointing out that some applications naturally lend themselves to
select * is all.