Re: [BUGS] CREATE VIEW interp AS select DISTINCT itemkey from songs; - Mailing list pgsql-bugs

Peter Schaefer <schaefer@cys.de> writes:
> codiak=> CREATE VIEW interp AS select DISTINCT ON id id from interp;

What version are you using?  The current development sources don't like
the above at all:

regression=> CREATE VIEW interp AS select DISTINCT ON id id from interp;
ERROR:  Relation 'interp' does not exist
regression=> create table interp (id int);
CREATE
regression=> CREATE VIEW interp AS select DISTINCT ON id id from interp;
ERROR:  Relation 'interp' already exists
regression=> CREATE VIEW interp1 AS select DISTINCT ON id id from interp;
ERROR:  DISTINCT not supported in views

The reason for the last point is that DISTINCT requires sorting, and
the current implementation method for views doesn't allow a view to
specify an ordering.  (CREATE VIEW ... SELECT ... ORDER BY doesn't
work either.)

You can work around this to some extent by using GROUP BY:

regression=> CREATE VIEW interp1 AS select id from interp group by id;
CREATE

although I think there may be some restrictions on grouped views too.

            regards, tom lane