I've found another difference between Postgre (7.03) and Oracle.
Postgre doesn't seem to count rows in a view properly. If I define
the following (assuming the users table is already defined):
openacs=# create table photos (
photo_id integer not null primary key,
user_id integer references users,
creation_date datetime
);
openacs=# create view new_users as
select user_id, min(creation_date) first_photo_date from photos
group by user_id;
openacs=# select * from photos;
photo_id | user_id | creation_date
----------+---------+------------------------
1 | 3 | 2000-12-05 00:00:00-05
2 | 3 | 2000-12-28 22:20:36-05
3 | 4 | 2000-12-27 00:00:00-05
4 | 4 | 2000-12-26 00:00:00-05
5 | 4 | 2000-12-26 00:00:00-05
6 | 4 | 2000-12-28 22:21:22-05
(6 rows)
openacs=# select * from new_users;
user_id | min
---------+------------------------
3 | 2000-12-05 00:00:00-05
4 | 2000-12-26 00:00:00-05
(2 rows)
Now, if i do
select count(*) from new_users, I
would expect to get the answer 2 (and that's what Oracle does).
Postgre responds:
openacs=# select count(*) from new_users;
count
-------
2
4
(2 rows)
So Postgre seems to count the rows inside the view instead of
the rows that the view returns. I'm not sure if this is a bug, or
simply a different way of doing things...
