Thread: function error
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
Needs a FOR!! system ALIAS FOR $1; Strange that the sample is wrong? http://www.pgexplorer.com ----- Original Message ----- From: "Travis Hoyt" <thoyt@npc.net> To: <pgsql-sql@postgresql.org> Sent: Wednesday, March 27, 2002 9:59 PM Subject: [SQL] function error > 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 >
Doh! Sorry my code DOES have the FOR in it as does the example in the
book. Neither works though.
My apologies this was my second attempt w/o the FOR.
Here's what I really have:
CREATE FUNCTION biwklyavg(text, text, text) RETURNS numeric AS ' DECLARE system ALIAS
FOR$1; startdate ALIAS FOR $2; enddate ALIAS FOR $3; result
numeric;
BEGIN result := (select (avg(usr) + avg(sys)) from sardata
where systemid = system and time between startdate and enddate; END;
' LANGUAGE 'sql';
-----Original Message-----
From: PG Explorer [mailto:pgmail@pgexplorer.com]
Sent: Wednesday, March 27, 2002 3:06 PM
To: Travis Hoyt; pgsql-sql@postgresql.org
Subject: Re: [SQL] function error
Needs a FOR!!
system ALIAS FOR $1;
Strange that the sample is wrong?
http://www.pgexplorer.com
----- Original Message -----
From: "Travis Hoyt" <thoyt@npc.net>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, March 27, 2002 9:59 PM
Subject: [SQL] function error
> 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
>
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'; What about using a language that actually supports the above syntax, like plpgsql? Jan > > 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 -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
On Wed, 27 Mar 2002, Travis Hoyt wrote: > Doh! Sorry my code DOES have the FOR in it as does the example in the > book. Neither works though. > > My apologies this was my second attempt w/o the FOR. > > Here's what I really have: > > CREATE FUNCTION biwklyavg(text, text, text) RETURNS numeric AS ' > DECLARE > system ALIAS FOR $1; > startdate ALIAS FOR $2; > enddate ALIAS FOR $3; > result numeric; > > BEGIN > result := (select (avg(usr) + avg(sys)) from sardata > where > systemid = system and time between startdate and enddate; > END; > ' LANGUAGE 'sql'; I think you want plpgsql not sql as the language. There does appear to be a missing close paren on the result:= thing as well.
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 <thoyt@npc.net> 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/
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 <thoyt@npc.net> 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/