Thread

1. Use a view as opposed to a table and it doesn't work!

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-11-21T17:11:53Z

   Gavin Evans (gavin@consultant.com) reports a bug with a severity of 1
   The lower the number the more severe it is.
   
   Short Description
   Use a view as opposed to a table and it doesn't work!
   
   Long Description
   If you perform the SQL below using a view to hold the avg(salary) you only get 1 row returned: 
    name | salary | avg_sal  | sal_diff
   ------+--------+----------+----------
    mike |   1500 | 1948.646 | -448.646
   
   if you comment out the 1st line and un comment the 2nd it works as expected returning:
     name   | salary  | avg_sal  | sal_diff
   ---------+---------+----------+-----------
    mike    |    1500 | 1948.646 |  -448.646
    sally   |   877.5 | 1948.646 | -1071.146
    georgia |         | 1948.646 |
    ted     | 2615.73 | 1948.646 |   667.084
    edna    |    2000 | 1948.646 |    51.354
    malcolm |    2750 | 1948.646 |   801.354
   (6 rows)
   
   
   I have tested the same code on INGRES and it works fine.  Therefore I am assuming IT MUST be a bug in postgresql.  Please correct me if I am wrong.
   
   Best regards
   
   Gavin Evans
   
   Sample Code
   CREATE VIEW avg_int AS SELECT AVG(salary) AS avg_sal FROM employee;
   --SELECT AVG(salary) AS avg_sal INTO TEMP TABLE avg_int FROM employee;
   CREATE VIEW average AS SELECT employee.name, employee.salary, avg_int.avg_sal, (salary-avg_sal) as sal_diff FROM 
   
   employee, avg_int;
   
   SELECT * FROM average;
   DROP VIEW average;
   DROP VIEW avg_int;
   
   
   --------------------- base table sql creation code ------------------
   
   create table employee(
   name varchar(10) not null,
   age integer,
   salary float,
   deptname varchar(10),
   manager varchar(10),
   primary key(name));
   
   insert into employee
   values ('mike', 29, 1500.00, 'shoe', 'edna');
   insert into employee
   values ('sally', 42, 877.50, 'toy', 'ted');
   insert into employee
   (name, age, deptname)
   values ('georgia', 22, 'book');
   insert into employee
   (name, salary, deptname, manager)
   values ('ted', 2615.73, 'toy', 'malcolm');
   insert into employee
   values ('edna', 39, 2000.00, 'shoe', 'malcolm');
   insert into employee
   (name, age, salary, deptname)
   values ('malcolm', 50, 2750.00, 'admin'); 
   
   
   
   No file was uploaded with this report
   
   
   

2. Re: Use a view as opposed to a table and it doesn't work!

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-21T18:05:53Z

   pgsql-bugs@postgresql.org writes:
   > Use a view as opposed to a table and it doesn't work!
   
   This is a longstanding limitation in Postgres views.  You can't do
   multiple levels of aggregation in a single query, and a view does
   not get you past that.
   
   It's fixed for 7.1, however.  In current sources I get the expected
   answer.
   
   			regards, tom lane