Received: from charon.npc.net (charon.npc.net [199.15.61.3]) by postgresql.org (Postfix) with ESMTP id 92E9F475F8D for ; Thu, 28 Mar 2002 10:51:54 -0500 (EST) Received: from mail.npc.net ([172.20.0.18]) by charon.npc.net (8.11.3/8.11.3) with ESMTP id g2SFgwn32295 for ; Thu, 28 Mar 2002 10:42:59 -0500 (EST) Received: from hoyltr1 ([172.20.0.114]) by mail.npc.net (Netscape Messaging Server 4.15) with SMTP id GTOXEM00.VZP; Thu, 28 Mar 2002 10:51:58 -0500 From: "Travis Hoyt" To: "Jeff Eckermann" , Subject: Re: function error Date: Thu, 28 Mar 2002 10:51:37 -0500 Message-ID: MIME-Version: 1.0 X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) In-Reply-To: <20020328153525.78708.qmail@web20806.mail.yahoo.com> Importance: Normal Content-Type: multipart/signed; micalg=SHA1; protocol="application/x-pkcs7-signature"; boundary="----=_NextPart_000_0810_01C1D646.87D40B40" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200203/380 X-Sequence-Number: 7254 This is a multi-part message in MIME format. ------=_NextPart_000_0810_01C1D646.87D40B40 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit My sincerest apologies. I had mistakenly posted a hacked up "let's try this" version of a function I had created that wasn't working. The truth is, everything you mentioned below IS in the book and written very well. I removed the FOR statement, the RETURN statement and the language definition being SQL instead of PLPGSQL was also my fault. Additionally, my variable types are wrong (my fault too). So what am I trying to say...it's all my fault. PostgreSQL Developer's Handbook is really a great book! It's just dangerous in the hands of idiots like me! Pause for time to open mouth and insert foot. Oh the humanity! Shamefully, Travis -----Original Message----- From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com] Sent: Thursday, March 28, 2002 10:35 AM To: Travis Hoyt; pgsql-sql@postgresql.org Subject: Re: [SQL] function error If this is genuinely a copy of something in the "PostgreSQL Developer's Handbook", then that book could use some work. 1. You need "ALIAS FOR" in the DECLARE section 2. You need an explicit RETURN; i.e. write "RETURN result;" before "END;" 3. Language is plpgsql, not sql (which doesn't allow the constructions you are using). If columns "systemid" and "time" are not of text type, you will need to do some type casting for your comparisons to work. I believe "time" is a reserved word anyway, so if that is the real name of the column, you will need to double-quote it, which in the function definition will need to be escaped: '"time"', or \"time\". --- Travis Hoyt wrote: > CREATE FUNCTION biwklyavg(text, text, text) RETURNS > numeric AS ' > DECLARE > system ALIAS $1; > startdate ALIAS $2; > enddate ALIAS $3; > result numeric; > > BEGIN > result := (select (avg(usr) + > avg(sys)) from sardata where > systemid = system > and time between startdate > and enddate; > END; > ' LANGUAGE 'sql'; > > Hello, > > The fuction listed above gives the following error: > > ERROR: parser: parse error at or near "alias" > > I'm running v7.2. Any ideas? I pulled this > directly from the examples in > the PostgreSQL Developer's Handbook. > > Thanks, > > Travis > > ATTACHMENT part 2 application/x-pkcs7-signature name=smime.p7s __________________________________________________ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards. http://movies.yahoo.com/ ------=_NextPart_000_0810_01C1D646.87D40B40 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIIJIDCCAr4wggInoAMCAQICAwZt8zANBgkqhkiG9w0BAQIFADCBkjEL MAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJ Q2FwZSBUb3duMQ8wDQYDVQQKEwZUaGF3dGUxHTAbBgNVBAsTFENlcnRpZmlj YXRlIFNlcnZpY2VzMSgwJgYDVQQDEx9QZXJzb25hbCBGcmVlbWFpbCBSU0Eg MjAwMC44LjMwMB4XDTAyMDEwMzE2MjAwN1oXDTAzMDEwMzE2MjAwN1owdjEN MAsGA1UEBBMESG95dDEPMA0GA1UEKhMGVHJhdmlzMRQwEgYDVQQDEwtUcmF2 aXMgSG95dDEcMBoGCSqGSIb3DQEJARYNdGhveXRAbnBjLm5ldDEgMB4GCSqG SIb3DQEJARYRdC5ob3l0QGFsbHRlbC5uZXQwgZ8wDQYJKoZIhvcNAQEBBQAD gY0AMIGJAoGBANbRK60nLCiGPa34o7ADVuScL3D8gq+E51OuCHbHMihMG+FF Onps8sgoj+UPqOBlYCJoEfmk3PaYaZyxfJvpdRWcJAuMFKIYKuTk9WtJoitf NBegwSnt50agD9+R0lwBHQFUq4fzLL3TTBFLP98CIGCi7162Xh8b7YxrxotQ Ins3AgMBAAGjPTA7MCsGA1UdEQQkMCKBDXRob3l0QG5wYy5uZXSBEXQuaG95 dEBhbGx0ZWwubmV0MAwGA1UdEwEB/wQCMAAwDQYJKoZIhvcNAQECBQADgYEA OgHIL+z1uJS29KQGHtr1Dy9Deal7Co4KsW8v8nvi1F8kmQFaAUZRj9I7IsTY qc9oqjbEVREq/e760H+Yu0UxHhMgCfSpnSlUjd/uUb+IGFAmrTHQ84G/9nnn lm3+9q5d1TRcQC985RdTlV+0dE+dtQS+hCsrwaE7+dpNmL2CbK4wggMpMIIC kqADAgECAgEMMA0GCSqGSIb3DQEBBAUAMIHRMQswCQYDVQQGEwJaQTEVMBMG A1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRvd24xGjAYBgNV BAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZpY2F0aW9u IFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwg RnJlZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWls QHRoYXd0ZS5jb20wHhcNMDAwODMwMDAwMDAwWhcNMDIwODI5MjM1OTU5WjCB kjELMAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UE BxMJQ2FwZSBUb3duMQ8wDQYDVQQKEwZUaGF3dGUxHTAbBgNVBAsTFENlcnRp ZmljYXRlIFNlcnZpY2VzMSgwJgYDVQQDEx9QZXJzb25hbCBGcmVlbWFpbCBS U0EgMjAwMC44LjMwMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDeMzKm Y8cJJUU+0m54J2eBxdqIGYKXDuNEKYpjNSptcDz63K737nRvMLwzkH/5NHGg o22Y8cNPomXbDfpL8dbdYaX5hc1VmjUanZJ1qCeu2HL5ugL217CR3hzpq+AY A6h8Q0JQUYeDPPA5tJtUihOH/7ObnUlmAC0JieyUa+mhaQIDAQABo04wTDAp BgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMS0yOTcwEgYD VR0TAQH/BAgwBgEB/wIBADALBgNVHQ8EBAMCAQYwDQYJKoZIhvcNAQEEBQAD gYEAcxtvJmWL/xU0S1liiu1EvknH6A27j7kNaiYqYoQfuIdjdBxtt88aU5FL 4c3mONntUPQ6bDSSrOaSnG7BIwHCCafvS65y3QZn9VBvLli4tgvBUFe17BzX 7xe21Yibt6KIGu05Wzl9NPy2lhglTWr0ncXDkS+plrgFPFL83eliA0gwggMt MIIClqADAgECAgEAMA0GCSqGSIb3DQEBBAUAMIHRMQswCQYDVQQGEwJaQTEV MBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRvd24xGjAY BgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZpY2F0 aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29u YWwgRnJlZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVt YWlsQHRoYXd0ZS5jb20wHhcNOTYwMTAxMDAwMDAwWhcNMjAxMjMxMjM1OTU5 WjCB0TELMAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAG A1UEBxMJQ2FwZSBUb3duMRowGAYDVQQKExFUaGF3dGUgQ29uc3VsdGluZzEo MCYGA1UECxMfQ2VydGlmaWNhdGlvbiBTZXJ2aWNlcyBEaXZpc2lvbjEkMCIG A1UEAxMbVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIENBMSswKQYJKoZIhvcN AQkBFhxwZXJzb25hbC1mcmVlbWFpbEB0aGF3dGUuY29tMIGfMA0GCSqGSIb3 DQEBAQUAA4GNADCBiQKBgQDUadfUsJRkW3HpR9gMUbbqcpGwhF59LQ2PexLf hSV1KHQ6QixjJ5+Ve0vvfhmHHYbqo925zpZkGsIUbkSsfOaP6E0PcR9AOKYA o4d49vmUhl6t6sBeduvZFKNdbnp8DKVLVX8GGSl/npom1Wq7OCQIapjHsdqj mJH9edvlWsQcuQIDAQABoxMwETAPBgNVHRMBAf8EBTADAQH/MA0GCSqGSIb3 DQEBBAUAA4GBAMfskn5O+PWWpWdiKqTwTRFg0G+NYFhhrCa7UjVcCM8w+6hK loofYkIjjBcP9LpknBesRynfnZhe0mxgcVyirNx54+duAEcftQ0o6AKd5Jr9 E/Sm2Xyx+NxfIyYJkYBz0BQb3kOpgyXy5pwvFcr+pquKB3WLDN1RhGvk+NHO d6KBMYICqjCCAqYCAQEwgZowgZIxCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxX ZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEPMA0GA1UEChMGVGhh d3RlMR0wGwYDVQQLExRDZXJ0aWZpY2F0ZSBTZXJ2aWNlczEoMCYGA1UEAxMf UGVyc29uYWwgRnJlZW1haWwgUlNBIDIwMDAuOC4zMAIDBm3zMAkGBSsOAwIa BQCgggFlMBgGCSqGSIb3DQEJAzELBgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkF MQ8XDTAyMDMyODE1NTEzNVowIwYJKoZIhvcNAQkEMRYEFBjAiwXuw2jz8ZGt LotWPcjEBLO9MFgGCSqGSIb3DQEJDzFLMEkwCgYIKoZIhvcNAwcwDgYIKoZI hvcNAwICAgCAMAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMAcGBSsOAwIaMAoG CCqGSIb3DQIFMIGrBgkrBgEEAYI3EAQxgZ0wgZowgZIxCzAJBgNVBAYTAlpB MRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEP MA0GA1UEChMGVGhhd3RlMR0wGwYDVQQLExRDZXJ0aWZpY2F0ZSBTZXJ2aWNl czEoMCYGA1UEAxMfUGVyc29uYWwgRnJlZW1haWwgUlNBIDIwMDAuOC4zMAID Bm3zMA0GCSqGSIb3DQEBAQUABIGAAYNgESXF++AcflO0tZghJRlWUz7QyRWS i9lLX3qbB4OuaEZ341Dv7CceRoZg/Rnt5WVMQvCNtrePdUTanXlS6TwUiTU3 tU2wLc0Yl5PUSL2JLe91BvGmD2zTYKUpP3ETzo4I4wkltGD8GcJs7JmD211T R/MrQGWB82G/6RkfsYsAAAAAAAA= ------=_NextPart_000_0810_01C1D646.87D40B40--