Skip site navigation (1) Skip section navigation (2)

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

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Use a view as opposed to a table and it doesn't work!
Date: 2000-11-21 17:11:53
Message-ID: 200011211711.eALHBrx61708@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Gavin Evans (gavin(at)consultant(dot)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


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2000-11-21 18:05:53
Subject: Re: Use a view as opposed to a table and it doesn't work!
Previous:From: Joseph ShraibmanDate: 2000-11-20 19:54:50
Subject: Re: [INTERFACES] Re: no meaningful way to determine fe or be version?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group