Views, aggregations, and errors

From: Michael Davis <michael(dot)davis(at)prevuenet(dot)com>
To: hackers(at)postgresql(dot)org
Subject: Views, aggregations, and errors
Date: 1999-03-30 16:11:57
Message-ID: 93C04F1F5173D211A27900105AA8FCFC1452F6@lambic.prevuenet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have a view:

DROP VIEW InvoiceSum;
CREATE VIEW InvoiceSum as
SELECT i.InvoiceID, i.DatePrinted, il.MemberID,
sum((il.UnitPrice * il.Quantity) + il.ShippingHandling) AS AmountOfInvoice
FROM Invoice i, InvoiceLines il
WHERE i.InvoiceID = il.InvoiceID
group by i.InvoiceID, i.DatePrinted, il.MemberID;

The following works great:

select * from invoicesum where memberid = 685;

The following fails:

select MemberID, sum(AmountOfInvoice) as InvAmt
from InvoiceSum
where memberid = 685;

ERROR: Illegal use of aggregates or non-group column in target list

The following also fails:

select MemberID, sum(AmountOfInvoice) as InvAmt
from InvoiceSum
where memberid = 685
group by memberid;

ERROR: ExecAgg: Bad Agg->Target for Agg 0
I get this error with or without the where clause.

I have many complex queries like this that I would like (need) to port to
PostgreSQL. As a result, this limitation will be difficult for me to work
around. I would be willing to explore fixing this for 6.6 if someone would
be willing to point me in the right direction and tell me where to start
looking in the code and possibly what to look for. The more information the
better.

I would also like to make views updateable without having to add rules.

The other limitation that is presenting some challenges is the lack of outer
joins. Is any portion of outer join supported? Could I find out when outer
join support is planned for implementation?

Thanks, Michael

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 1999-03-30 16:31:35 Re: [SQL] indexing a datetime by date
Previous Message Bruce Momjian 1999-03-30 15:57:17 Re: [HACKERS] vacuum updated...