Re: Compile error in function

On Sat, 16 Aug 2003, big_mafa wrote:

> There is a very annoying problem.
> I created a very simple function, which can be created well, but if I call
> it, the following problem occurs:

The function isn't checked until first run.

> ERROR: parse error at or near "IF"
> WARNING: plpgsql: ERROR during compile of cre_kitchen_log near line 1
>
> The Function is:
>
> CREATE OR REPLACE FUNCTION "public"."cre_kitchen_log" (date) RETURNS integer
> AS'

You need a BEGIN here.

> IF not exists(select count(*) from kitchenlog where dat = $1) THEN

This is not likely to do what you want. select count(*) always returns a
row so not exists should probably always return false. I would say
you'd want something like:
IF not exists(select * from kitchenlog where dat=$1) THEN

However, you're making this CALLED ON NULL INPUT, but this also won't work
if $1 is NULL since dat=NULL will never be true for the above. Do you
really want it to work for the NULL date? If so the inner condition
should probably be like: where dat=$1 or (dat is null and $1 is null)

> insert into kitchenlog(dat,prodid) select $1 , id from products where
> incl=''T'';
> END IF;
> select 1 as result;

And it looks like you want a RETURN 1; and an END; here rather than that
select.

> 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

-----

CREATE OR REPLACE FUNCTION "public"."cre_kitchen_log" (date) RETURNS
integer
AS'
BEGIN
IF NOT EXISTS (select * from kitchenlog where dat = $1
or (dat is NULL and $1 is NULL)) THEN
insert into kitchenlog(dat,prodid) select $1 , id from products where
incl=''T'';
END IF;
RETURN 1;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;