Skip site navigation (1) Skip section navigation (2)

Re: Compile error in function

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: big_mafa <big_mafa(at)freemail(dot)hu>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Compile error in function
Date: 2003-08-16 19:49:54
Message-ID: 20030816123851.C64584-100000@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-novice
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;



In response to

Responses

pgsql-novice by date

Next:From: big_mafaDate: 2003-08-16 20:07:59
Subject: Re: Compile error in function
Previous:From: big_mafaDate: 2003-08-16 19:43:28
Subject: Re: Compile error in function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group