Re: [HACKERS] views and group by (formerly: create view as selec - Mailing list pgsql-hackers

Michael J Davis wrote:

>
> This implies that the "group by" clause is not supported in views.  I have
> created views that use the group by clause and they appear to work.  I have
> not verified the content of the records.  I would like to know more about
> what Jan means when he says that "group by" is not supported in views?  Does
> it mean that the content of the results could be unexpected or are they
> conditions where they may work and other conditions where they don't work?
> More info would be greatly appreciated.

    I tried to make it and it works partially. The problems arise
    if you have a view with a group by clause but do  not  select
    the attributes the group by clause uses:

      CREATE TABLE t1 (a int4, b int4);
      CREATE VIEW v1 AS SELECT b, count(b) FROM t1 GROUP BY b;

      SELECT count FROM v1;
      SELECT count(*) FROM v1;

    Both selects crash the backend!

    If  you have a view that uses GROUP BY and do a simple SELECT
    * from it, then it will work and return the correct  results.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #