Re: [HACKERS] Views on aggregates - need assistence

Brett McCormick wrote:
>
> Thank you for addressing this issue! It has been bugging me for a
> while. Usually I just select into a new table and select from that
> (but yes, it is multiple queries). Normally I want to do something
> like:
>
> select bar,count(a.oid) as c from a,b where a.ab = b.bar and c > 1;
^^^^^
This is what HAVING is for (unimplemented, yet).

>
> This actually seems to be a different issue with more complicated
> unresolvable (?) problems, because you want a pre-result (per
> combination of instances matched) where and a result where (per result
> tuple).. Is this possible to do using subqueries? I'll try to find out.

No, if you really want to see count in output. If you would be happy
with bar only then this could help:

select bar from b where 1 < (select count(*) from a where a.ab = b.bar);

(Having HAVING would be better, of 'course :)

> This might be totally unrelated, actually. I do not know enough about
> view system to understand unresolvable conflicts.

You could CREATE VIEW V as select bar,count(a.oid) as c from a,b
where a.ab = b.bar group by bar;
and then just select * from v where c > 1.

Vadim