Re: [HACKERS] views and group by (formerly: create view as selec

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(at)debis(dot)com (Jan Wieck) #