Thread: Problem with subquery joined to a view
If I've done something wrong, I'd like to figure that out too if anyone can help.
This forms a bunch of hypothetical payroll entries and then makes a view which aggregates them by some arbitrary time period. (I've used a function which pulls out the month to group by.) Then I do a query on the view with a subquery as one of the fields that pulls out only a subset of the entries in the group.
The third "select" uses a function to create the subquery. This works (and is my current work-around).
The fourth "select" uses a regular subquery. It gives the error:
psql:datbug.sql:44: ERROR: Sub-SELECT uses un-GROUPed attribute pay_req.wdate from outer query
drop table pay_req;
drop view pay_req_v;
drop function sumr(int4,text,text);
set DateStyle to 'ISO'
create table pay_req (
empl_id int4,
wdate date,
type varchar,
hours float8 not null check (hours >= 0),
primary key (empl_id, wdate)
);
create view pay_req_v as
select empl_id,substr(text(wdate),6,2) as month,sum(hours) as hours from pay_req group by 1,2;
create function sumr(int4,text,text) returns float8 as '
select coalesce(sum(hours),0) from pay_req where empl_id = $1 and type = $2 and substr(text(wdate),6,2) = $3;
' LANGUAGE 'sql';
insert into pay_req (empl_id,wdate,type,hours) values (1000,'2000-Jan-01','r',4);
insert into pay_req (empl_id,wdate,type,hours) values (1000,'2000-Jan-02','r',5);
insert into pay_req (empl_id,wdate,type,hours) values (1000,'2000-Jan-03','o',6);
insert into pay_req (empl_id,wdate,type,hours) values (1001,'2000-Jan-01','r',2);
insert into pay_req (empl_id,wdate,type,hours) values (1001,'2000-Jan-02','r',3);
insert into pay_req (empl_id,wdate,type,hours) values (1001,'2000-Jan-03','o',4);
select * from pay_req order by empl_id,wdate,type,hours;
select * from pay_req_v order by empl_id,month,hours;
select v.empl_id,month,hours,
sumr(v.empl_id,'r',v.month) as "type-r"
from pay_req_v v where
v.empl_id = 1000 and
v.month = '01'
;
select v.empl_id,month,hours,
(select coalesce(sum(r.hours),0) from pay_req r where r.type = 'r' and r.empl_id = v.empl_id and substr(text(r.wdate),6,2) = v.month) as "type-r"
from pay_req_v v where
v.empl_id = 1000 and
v.month = '01'
;
Attachment
Kyle <kyle@actarg.com> writes: > The fourth "select" uses a regular subquery. It gives the error: > psql:datbug.sql:44: ERROR: Sub-SELECT uses un-GROUPed attribute > pay_req.wdate from outer query This appears to work correctly in current sources. In 7.0.* and before grouped views do not work very well, because the rewriter effectively expands them in-line, thus converting your outer query into a grouped query (or making it not work at all, if it was already grouped :-(). See related bug report just a few minutes ago on pgsql-bugs. regards, tom lane