Problem with subquery joined to a view
От | Kyle |
---|---|
Тема | Problem with subquery joined to a view |
Дата | |
Msg-id | 3A1AB618.E1B856FA@actarg.com обсуждение исходный текст |
Ответы |
Re: Problem with subquery joined to a view
|
Список | pgsql-sql |
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'
;
Вложения
В списке pgsql-sql по дате отправления: