Re: SQL feature requests

"Ben Tilly" <btilly(at)gmail(dot)com> writes:

> Hopefully this is the right place for a few feature requests that
> would address some of the things that I've noticed in postgres.
>
> 1. Just a minor annoyance, but why must subqueries in FROM clauses
> have an alias? For instance suppose that I have an orders table, and
> one of the fields is userid. The following is unambiguous and is
> legal in Oracle:

Thank you, this is one of my top pet peeves but when I proposed changing it I
was told nobody's complained. Now we have at least one user complaint, any
others out there?

> 2. Why is 'non-integer constant in GROUP BY' an error?

Hm... I was a bit surprised by this warning myself. IIRC there was an
implementation convenience issue.

> 3. How hard would it be to have postgres ignore aliases in group by
> clauses?

That sounds like a strange idea.

> 4) Items 2 and 3 would both be made irrelevant if postgres did
> something that I'd really, really would like. Which is to assume that
> a query without a group by clause, but with an aggregate function in
> the select, should have an implicit group by clause where you group by
> all non-aggregate functions in the select.
>
> For example
>
> SELECT foo, count(*)
> FROM bar
>
> would be processed as:
>
> SELECT foo, count(*)
> FROM bar
> GROUP BY foo

I agree this would be convenient but it seems too scary to actually go
anywhere. What would you group by in the case of:

SELECT a+b, count(*) FROM bar

Should it group by a,b or a+b ?

Also, this might be a bit shocking for MySQL users who are accustomed to
MySQL's non-standard extension for the same syntax. There it's treated as an
assertion that the columns are equal for all records in a group or at least
that it doesn't matter which such value is returned, effectively equivalent to
our DISTINCT ON feature.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com