Обсуждение: Compile error in function

Hi!

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:
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'
IF not exists(select count(*) from kitchenlog where dat = $1) THEN
insert into kitchenlog(dat,prodid) select $1 , id from products where
incl=''T'';
END IF;
select 1 as result;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;


I created this function using EMS PostgreSQLManager. I Copied it to pgsql
(to be sure, there is no CR/LF in code) - but the result is the same.

Could someone help me? Thanks! Attila

Of course, in the function I wanted to use "*" , not "count(*)" , this
mistake is because of I tried everything...

Thanks in advance again!

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;

Hi Stephan,

Thanks for your very fast answer - of course, the begin was the problem...

Koszonom!

Regards: Attila


----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "big_mafa" <big_mafa@freemail.hu>
Cc: <pgsql-novice@postgresql.org>
Sent: Saturday, August 16, 2003 9:49 PM
Subject: Re: [NOVICE] 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.