Re: [BUGS] CREATE VIEW interp AS select DISTINCT itemkey from songs;

Peter Schaefer <schaefer(at)cys(dot)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