Re: POSS. FEATURE REQ: "Dynamic" Views - Mailing list pgsql-general

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