Long story !!! Please HELP !!!

hi there,

I'm new to the list AND have one big problem.(pls. don't care for some
syntax errors, I'm writing this on the fly).
Let me explain the thing from the begining. Before a 2-3 days I began
making a REPORTS (for one project I'm doing).
I decided to use Postgres 6.4.2 (RH6) 'cause it is free and support
SubSelsects & Views ?!?! (it will be good to support OUTER JOINS but
..:")).

So, I have the folowing tables created as VIEWS :

View1
id | amount |
---------
2 | 1000 |
5 | 300 |
__________

View2
id | hit |
---------
1 | 1 |
2 | 3 |
5 | 1 |
__________

They are created like this (pls. read to the end :"))
<conditions> contains subselect and other stuff:

CREATE VIEW View1 AS
SELECT X.id, SUM(Y.amount) ...
FROM X, Y
WHERE <conditoins>

CREATE VIEW View2 AS
SELECT X.id, COUNT(Y.hit) ...
FROM X, Y
WHERE <conditoins>

Then I decided to combine the results (I used UNION to simulate OUTER
JOIN, but at the moment I will stop only on the first select of the
JOIN). OK here is it :

SELECT v1.id, v1.amount, v2.hit
FROM view1 AS v1, view2 AS v2
WHERE v1.id = v2.id

UNION

....second select ...

What was my surprise that the result was :

Result
id | amount | hit |
--------------
2 | 3000 | 6 |
5 | 900 | 3 |
_______________

??!?!!?.OK I made a search in mail archives and saw that the views has
some problems !?. Then dloaded the new .rpm Postgres 6.5.x and installed
it (I thought the problem is resolved, but ... :"(, NO AS I READ in THE
NEW DOCS USING AGGREGATES IN VIEWS IS BROKEN DOESN"T WORK).
Even if I make the views in this way :

CREATE VIEW View1 AS
SELECT X.id, Y.amount
FROM X, Y
WHERE <conditoins>

CREATE VIEW View2 AS
SELECT X.id, Y.hit
FROM X, Y
WHERE <conditoins>

and then :

SELECT v1.id, SUM(v1.amount), SUM(v2.hit)
FROM view1 AS v1, view2 AS v2
WHERE v1.id = v2.id

It again doesn't work. So I read at the new docs again that there is a
way to do this as FUNCTION (no comments how :"(). So I made something
like this (it works, but ... ):

CREATE FUNCTION View1Sum(oid) RETURN float8 AS
' SELECT Sum(View1.amount) FROM View1 WHERE id = $1;'
LANGUAGE 'sql'

CREATE FUNCTION View2Sum(oid) RETURN float8 AS
' SELECT Sum(View2.hit) FROM View2 WHERE id = $1;'
LANGUAGE 'sql'

I don't how can pass the names of the fields or tables as parameters (if
this is possible at all ?!?), like this :

CREATE FUNCTION ViewSum(text, text, text) RETURN float8 AS
' SELECT Sum($2.$1) FROM $2 WHERE $3;'
LANGUAGE 'sql'

($1 - field name, $2 - table name, $3 - condition as text)
:"(::::::::::::::::
NOPE.
Why I want this ? 'cause the views are created on the fly by Perl
script,so if I use this first method, I must create also the functions
on the fly, BAD. (Every view has a unique name)
Another reason 'cause "functions way" is bad solution is 'cause If I
have 1000 rows result, for every id I must execute similar function ~6
times (my situation is more complicated than this I'm describing here)
i.e. 6000 SELECT for doing agregates ?!?!:"(

PLEASE TELL ME IS THERE BETTER SOLUTION TO THIS !!! PLEASE.

I'm in the middle of the project and don't know what to do, it is a bad
time to change the DB. Is there any other free DB that "REALLY" supports
SubSelects, Views and OUTER JOINS.

PS. If I use SELECT INTO instead CREATE VIEW all is working fine, but
this isn't the solution, 'cause it is better to use text file instead DB
:") - it will be faster I think :")

Thax alot in advance.
=====
iVAN
raptor(at)unacs(dot)bg
=====