Re: Why is this function wrong

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
>
> .
>