I have the following aggregate functions (AGG FUNCs):

foo(), foobar(), fredstats(), barneystats().

I want to know if I can use multiple AGG FUNCs in an algebraic expression. This may seem a strange/simplistic question for seasoned SQL developers - however, the but the reason I ask is that so far, all AGG FUNCs examples I have seen are of the simplistic variety e.g. max(salary) < 100, rather than using the AGG FUNCs in an expression which involves using multiple AGG FUNCs in an expression (like agg_func1() > agg_func2()).

The information below should help clarify further.

Given tables with the following schemas:

CREATE TABLE item (id int, length float, weight float);
CREATE TABLE item_info (item_id, name varchar(32));

# Is it legal (ANSI) SQL to write queries of this format ?

SELECT id, name, foo, foobar, fredstats FROM A, B
    (SELECT id, foo(123) as foo, foobar('red') as foobar, fredstats('weight') as fredstats 
       FROM item GROUP BY id HAVING [ALGEBRAIC EXPRESSION] ORDER BY id AS A), 
    item_info AS B
WHERE item.id = B.id

Where:

ALGEBRAIC EXPRESSION is the type of expression that can be used in a WHERE clause - for example:

((foo(x) < foobar(y)) AND foobar(y) IN (1,2,3)) OR (fredstats(x) <> 0))

I am using PostgreSQL as the db, but I would prefer to use ANSI SQL wherever possible.

Assuming it is legal to include AGG FUNCS in the way I have done above, I'd like to know:

1. Is there a more efficient way to write the above query ?

2. Is there any way I can speed up the query in terms of a judicious choice of indexes on the tables item and item_info ?

3. Is there a performance hit of using AGG FUNCs in an algebraic expression like I am (i.e. an expression involving the output of aggregate functions rather than constants?

4. Can the expression also include 'scaled' AGG FUNC? (for example: 2*foo(123) < -3*foobar(456) ) - will scaling (i.e. multiplying an AGG FUNC by a number have an effect on performance?)

5. How can I write the query above using INNER JOINS instead?