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;
