Received: from mail.opentec.com.au ([203.5.51.3]) by hub.org (8.10.1/8.10.1) with ESMTP id e665BeS53475 for ; Thu, 6 Jul 2000 01:11:45 -0400 (EDT) Received: from opentec.com.au (ochre) by mail.opentec.com.au (Sun Internet Mail Server sims.3.5.1999.07.30.00.05.p8) with ESMTP id <0FX900102FYINY@mail.opentec.com.au> for pgsql-sql@hub.org; Thu, 6 Jul 2000 15:15:55 +1000 (EST) Date: Thu, 06 Jul 2000 15:19:51 +1000 From: Paul McGarry Subject: Re: ERROR: ExecEvalAggref: no aggregates in thisexpressioncontext To: Tom Lane Cc: pgsql-sql@hub.org Message-id: <396416F7.F5603074@opentec.com.au> MIME-version: 1.0 X-Mailer: Mozilla 4.61 [en] (Win98; I) Content-type: multipart/mixed; boundary="------------1FE5E2B6508BC303C50351B6" X-Accept-Language: en References: <200007042201.e64M1GS36467@hub.org> <3962834C.5EF48AB7@opentec.com.au> <3397.962817322@sss.pgh.pa.us> <3963DA0C.B248C79D@opentec.com.au> <20348.962858664@sss.pgh.pa.us> X-Archive-Number: 200007/55 This is a multi-part message in MIME format. --------------1FE5E2B6508BC303C50351B6 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote: > View? Hmm, we know that views involving GROUP BY or aggregates > don't work very well. We hope to fix those problems in 7.2, but > right now there's probably not much that can be done about it. > I'd recommend avoiding views that use those features for now. Yes, I've just discovered the Todo list :) Oh well, it probably needed rethinking anyway.....I want fast retrieval over fast update so I probably want to do my min() and max() magic with triggers on insert/update/delete rather than on each select with the view. In any case I have attached some SQL which sets up tables and generates the ExecEvalAggref I was getting in case it is of any use to anyone. Cheers. -- Paul McGarry mailto:paulm@opentec.com.au Systems Integrator http://www.opentec.com.au Opentec Pty Ltd http://www.iebusiness.com.au 6 Lyon Park Road Phone: (02) 9878 1744 North Ryde NSW 2113 Fax: (02) 9878 1755 --------------1FE5E2B6508BC303C50351B6 Content-Type: application/x-unknown-content-type-sql_auto_file; name="execeval.sql" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="execeval.sql" LyogU2NyaXB0IGRlc2lnbmVkIHRvIGRlbW9uc3RyYXRlIHNlbGVjdCBjb3Vu dCgqKSBwcm9ibGVtOyAqLw0KDQpDUkVBVEUgVEFCTEUgZW50cnkoDQoJZW50 cnlfaWQgSU5UNCBQUklNQVJZIEtFWSwNCgllbnRyeV9kZXNjcmlwdGlvbl90 ZXh0IFRFWFQsDQoJZW50cnlfbGl2ZSBib29sIE5PVCBOVUxMIERFRkFVTFQg J3QnDQopOw0KR1JBTlQgQUxMIG9uIGVudHJ5IFRPIFBVQkxJQzsNCklOU0VS VCBJTlRPIGVudHJ5IFZBTFVFUyAoMSwnZW50cnkgb25lJywndCcpOw0KSU5T RVJUIElOVE8gZW50cnkgVkFMVUVTICgyLCdlbnRyeSB0d28nLCd0Jyk7DQpJ TlNFUlQgSU5UTyBlbnRyeSBWQUxVRVMgKDMsJ2VudHJ5IHRocmVlJywndCcp Ow0KDQpDUkVBVEUgVEFCTEUgZGV0YWlsICgNCglkZXRhaWxfaWQgSU5UNCBQ UklNQVJZIEtFWSwNCgllbnRyeV9pZCBJTlQ0IE5PVCBOVUxMIFJFRkVSRU5D RVMgZW50cnkgT04gREVMRVRFIE5PIEFDVElPTiwNCglhbW91bnQgSU5UNCBO T1QgTlVMTA0KKTsNCkdSQU5UIEFMTCBPTiBkZXRhaWwgdG8gUFVCTElDOw0K Q1JFQVRFIElOREVYIGRldGFpbF9lbnRyeV9pZF9pZHggT04gZGV0YWlsIChl bnRyeV9pZCk7DQpJTlNFUlQgSU5UTyBkZXRhaWwgVkFMVUVTICgxLDEsMSk7 DQpJTlNFUlQgSU5UTyBkZXRhaWwgVkFMVUVTICgyLDEsNSk7DQpJTlNFUlQg SU5UTyBkZXRhaWwgVkFMVUVTICgzLDIsMik7DQpJTlNFUlQgSU5UTyBkZXRh aWwgVkFMVUVTICg0LDIsMTAwKTsNCklOU0VSVCBJTlRPIGRldGFpbCBWQUxV RVMgKDUsMyw3KTsNCg0KQ1JFQVRFIFRBQkxFIGVudHJ5X2Rlc2NfZnRpIChz dHJpbmcgdmFyY2hhcigyNSksIGlkIElOVDQpOw0KR1JBTlQgQUxMIE9OIGVu dHJ5X2Rlc2NfZnRpIFRPIFBVQkxJQzsNCklOU0VSVCBJTlRPIGVudHJ5X2Rl c2NfZnRpIFZBTFVFUyAoJ29uZScsMSk7DQoNCg0KDQpDUkVBVEUgVklFVyBl bnRyeV92aWV3IEFTDQpTRUxFQ1QgZW50cnkuZW50cnlfaWQgYXMgaWQsDQog ICAgICAgZW50cnkuZW50cnlfZGVzY3JpcHRpb25fdGV4dCBhcyBkZXNjcmlw dGlvbl90ZXh0LA0KICAgICAgIG1pbiAoZGV0YWlsLmFtb3VudCkgYXMgbWlu YW1vdW50DQpGUk9NICAgZW50cnksDQogICAgICAgZGV0YWlsDQpXSEVSRSAg ZW50cnlfbGl2ZT0ndCcNCiAgQU5EICBkZXRhaWwuZW50cnlfaWQ9ZW50cnku ZW50cnlfaWQNCkdST1VQIEJZIA0KICAgICAgZW50cnkuZW50cnlfaWQsDQog ICAgICBlbnRyeS5lbnRyeV9kZXNjcmlwdGlvbl90ZXh0DQo7DQpHUkFOVCBT RUxFQ1QgT04gZW50cnlfdmlldyBUTyBQVUJMSUM7DQoNCg0KU0VMRUNUIGNv dW50KGVudHJ5X2Rlc2NfZnRpLmlkKSBhcyByYXRpbmcsIA0KICAgICAgIGVu dHJ5X3ZpZXcuKiANCkZST00gZW50cnlfdmlldywgDQogICAgIGVudHJ5X2Rl c2NfZnRpIA0KV0hFUkUgZW50cnlfZGVzY19mdGkuaWQ9ZW50cnlfdmlldy5p ZCANCiAgICAgIGFuZCBlbnRyeV9kZXNjX2Z0aS5zdHJpbmcgfideb25lJyAN CkdST1VQIEJZIGVudHJ5X3ZpZXcuaWQsIA0KICAgICAgICAgZW50cnlfdmll dy5kZXNjcmlwdGlvbl90ZXh0LCANCiAgICAgICAgIGVudHJ5X3ZpZXcubWlu YW1vdW50Ow0KDQo= --------------1FE5E2B6508BC303C50351B6--