Search Postgresql Archives

Re: Why is this function wrong

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

.


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux