Обсуждение: Why is this function wrong
This is probably obvious but I am not seeing what is going wrong with this function definition: CREATE OR REPLACE FUNCTION "public"."cproc_hli" (bgtemp NUMERIC, humidity NUMERIC, wspeed NUMERIC) RETURNS NUMERIC AS IF ($1 < 25) THEN SELECT (10.66 * (0.28 * $2)+(1.3 * $1) - $3) ELSE SELECT (8.62 * (0.38 * $2) + (1.55 * $1) - (0.5 * $3) + (exp(- $3 + 2.4))) END IF LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER; ERROR: syntax error at or near "IF" at character 119 LINE 2: IF ($1 < 25) I have tried with dollar quoting around the function body, changing the maguage to sql and using CASE ... WHEN instead of IF , tried Brackets and no brackets around the Boolean expression.. and a number of other variations, as well as rereading the Documentation. I have also tried not having aliases/names for the arguments. In every case I always get the syntax error directly after "AS". Can somebody point what is probably obvious. Cheers Johan Wehtje
The variant you're showing here has absolutely no quoting for the function body. You need to quote the body, and escape the quotes you have inside the body (in this example you don't have any). Wrap the body in BEGIN ... END. Also put semicolons after each statement end. Corrected code: CREATE OR REPLACE FUNCTION "public"."cproc_hli" (bgtemp NUMERIC, humidity NUMERIC, wspeed NUMERIC) RETURNS NUMERIC AS ' BEGIN IF ($1 < 25) THEN SELECT (10.66 * (0.28 * $2)+(1.3 * $1) - $3); ELSE SELECT (8.62 * (0.38 * $2) + (1.55 * $1) - (0.5 * $3) + (exp(- $3 + 2.4))); END IF; END ' LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER; HTH, Csaba. On Mon, 2005-10-24 at 18:27, Johan Wehtje wrote: > This is probably obvious but I am not seeing what is going wrong with > this function definition: > > CREATE OR REPLACE FUNCTION "public"."cproc_hli" (bgtemp NUMERIC, > humidity NUMERIC, wspeed NUMERIC) RETURNS NUMERIC AS > IF ($1 < 25) > THEN > SELECT (10.66 * (0.28 * $2)+(1.3 * $1) - $3); > ELSE > SELECT (8.62 * (0.38 * $2) + (1.55 * $1) - (0.5 * $3) + (exp(- $3 + 2.4))) > END IF > LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER; > ERROR: syntax error at or near "IF" at character 119 > LINE 2: IF ($1 < 25) > > I have tried with dollar quoting around the function body, changing the > maguage to sql and using CASE ... WHEN instead of IF , tried Brackets > and no brackets around the Boolean expression.. and a number of other > variations, as well as rereading the Documentation. > > I have also tried not having aliases/names for the arguments. > > In every case I always get the syntax error directly after "AS". > > Can somebody point what is probably obvious. > > Cheers > Johan Wehtje > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
On Oct 24, 2005, at 11:27 AM, Johan Wehtje wrote: > This is probably obvious but I am not seeing what is going wrong > with this function definition: > > CREATE OR REPLACE FUNCTION "public"."cproc_hli" (bgtemp NUMERIC, > humidity NUMERIC, wspeed NUMERIC) RETURNS NUMERIC AS > IF ($1 < 25) > THEN > SELECT (10.66 * (0.28 * $2)+(1.3 * $1) - $3) > ELSE > SELECT (8.62 * (0.38 * $2) + (1.55 * $1) - (0.5 * $3) + (exp(- $3 + > 2.4))) > END IF > LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY > INVOKER; > > ERROR: syntax error at or near "IF" at character 119 > LINE 2: IF ($1 < 25) > > I have tried with dollar quoting around the function body, changing > the maguage to sql and using CASE ... WHEN instead of IF , tried > Brackets and no brackets around the Boolean expression.. and a > number of other variations, as well as rereading the Documentation. > > I have also tried not having aliases/names for the arguments. > > In every case I always get the syntax error directly after "AS". > > Can somebody point what is probably obvious. > > Cheers > Johan Wehtje This version will compile, but it won't do anything useful: CREATE OR REPLACE FUNCTION "public"."cproc_hli" (bgtemp NUMERIC, humidity NUMERIC, wspeed NUMERIC) RETURNS NUMERIC AS $$ BEGIN IF (bgtemp < 25) THEN PERFORM (10.66 * (0.28 * humidity)+(1.3 * bgtemp) - wspeed); ELSE PERFORM (8.62 * (0.38 * humidity) + (1.55 * bgtemp) - (0.5 * wspeed) + (exp(- wspeed + 2.4))); END IF; END; $$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER; You were missing some basic syntactic requirements, including a BEGIN/ END block, as well as several semicolons. You will also need to SELECT into a variable if you want anything useful to happen with the results. I've replaced your SELECTs with PERFORMs to recreate your function as originally written. I recommend a closer reading of the chapter on PL/pgSQL: http://www.postgresql.org/docs/8.0/static/plpgsql.html -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Open Source Solutions. Optimized Web Development. http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax)
Thanks for your help, I adjusted the function after a better reading of the pl/Sql chapter - and feel I posted in haste. Hopefully though it may prove useful to someone else. Cheers Johan Wehtje Thomas F. O'Connell wrote: > > On Oct 24, 2005, at 11:27 AM, Johan Wehtje wrote: > >> This is probably obvious but I am not seeing what is going wrong with >> this function definition: >> >> CREATE OR REPLACE FUNCTION "public"."cproc_hli" (bgtemp NUMERIC, >> humidity NUMERIC, wspeed NUMERIC) RETURNS NUMERIC AS >> IF ($1 < 25) >> THEN >> SELECT (10.66 * (0.28 * $2)+(1.3 * $1) - $3) >> ELSE >> SELECT (8.62 * (0.38 * $2) + (1.55 * $1) - (0.5 * $3) + (exp(- $3 + >> 2.4))) >> END IF >> LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER; >> >> ERROR: syntax error at or near "IF" at character 119 >> LINE 2: IF ($1 < 25) >> >> I have tried with dollar quoting around the function body, changing >> the maguage to sql and using CASE ... WHEN instead of IF , tried >> Brackets and no brackets around the Boolean expression.. and a number >> of other variations, as well as rereading the Documentation. >> >> I have also tried not having aliases/names for the arguments. >> >> In every case I always get the syntax error directly after "AS". >> >> Can somebody point what is probably obvious. >> >> Cheers >> Johan Wehtje > > This version will compile, but it won't do anything useful: > > CREATE OR REPLACE FUNCTION "public"."cproc_hli" (bgtemp NUMERIC, > humidity NUMERIC, wspeed NUMERIC) RETURNS NUMERIC AS $$ > BEGIN > IF (bgtemp < 25) THEN > PERFORM (10.66 * (0.28 * humidity)+(1.3 * bgtemp) - wspeed); > ELSE > PERFORM (8.62 * (0.38 * humidity) + (1.55 * bgtemp) - (0.5 * wspeed) + > (exp(- wspeed + 2.4))); > END IF; > END; > $$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER; > > You were missing some basic syntactic requirements, including a > BEGIN/END block, as well as several semicolons. You will also need to > SELECT into a variable if you want anything useful to happen with the > results. I've replaced your SELECTs with PERFORMs to recreate your > function as originally written. > > I recommend a closer reading of the chapter on PL/pgSQL: > > http://www.postgresql.org/docs/8.0/static/plpgsql.html > > -- > Thomas F. O'Connell > Co-Founder, Information Architect > Sitening, LLC > > Open Source Solutions. Optimized Web Development. > > http://www.sitening.com/ > 110 30th Avenue North, Suite 6 > Nashville, TN 37203-6320 > 615-469-5150 > 615-469-5151 (fax) > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > . >
Johan Wehtje wrote:
> This is probably obvious but I am not seeing what is going wrong with
> this function definition:
>
> CREATE OR REPLACE FUNCTION "public"."cproc_hli" (bgtemp NUMERIC,
> humidity NUMERIC, wspeed NUMERIC) RETURNS NUMERIC AS
> IF ($1 < 25)
> THEN
> SELECT (10.66 * (0.28 * $2)+(1.3 * $1) - $3)
> ELSE
> SELECT (8.62 * (0.38 * $2) + (1.55 * $1) - (0.5 * $3) + (exp(- $3 + 2.4)))
> END IF
> LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
>
> ERROR: syntax error at or near "IF" at character 119
> LINE 2: IF ($1 < 25)
>
> I have tried with dollar quoting around the function body, changing the
> maguage to sql and using CASE ... WHEN instead of IF , tried Brackets
> and no brackets around the Boolean expression.. and a number of other
> variations, as well as rereading the Documentation.
>
> I have also tried not having aliases/names for the arguments.
>
> In every case I always get the syntax error directly after "AS".
>
> Can somebody point what is probably obvious.
>
> Cheers
> Johan Wehtje
Don't you need BEGIN and END lines in there, e.g.:
CREATE OR REPLACE FUNCTION "public"."cproc_hli" (bgtemp NUMERIC,
humidity NUMERIC, wspeed NUMERIC) RETURNS NUMERIC AS
BEGIN
IF ($1 < 25) THEN
SELECT (10.66 * (0.28 * $2)+(1.3 * $1) - $3) ;
ELSE
SELECT (8.62 * (0.38 * $2) + (1.55 * $1) - (0.5 * $3) + (exp(- $3 +
2.4))) ;
END IF ;
END ;
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY
INVOKER;
?
slight modification to the previous post, sorry, see http://www.postgresql.org/docs/8.0/interactive/plpgsql-structure.html for the real docs. CREATE OR REPLACE FUNCTION "public"."cproc_hli" (bgtemp NUMERIC, humidity NUMERIC, wspeed NUMERIC) RETURNS NUMERIC AS $$ BEGIN IF ($1 < 25) THEN SELECT (10.66 * (0.28 * $2)+(1.3 * $1) - $3) ; ELSE SELECT (8.62 * (0.38 * $2) + (1.55 * $1) - (0.5 * $3) + (exp(- $3 + 2.4))) ; END IF ; END ; $$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;