Re: plpgsql trigger parse error

>>>>> "me" == Roland Roberts <roland(at)astrofoto(dot)org> writes:

me> Thanks to everyone who pointed out my problem.
me> I promise to ask my next question on the novices list.... :-/

Okay, I lied, I'm asking again. Even with the else if vs elsif
problem fixed, the function will not compile.

create function deepsky_nodups() returns opaque as '
begin
if (NEW.suffix is NULL and NEW.component is NULL) then
if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and suffix is null and component is null)) then
return NULL;
end if;
elsif (NEW.component is NULL) then
if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and suffix = NEW.suffix and component is null)) then
return NULL;
end if;
end if;
return NEW;
end;
' language 'plpgsql';

create trigger ds_nodups before insert on deepsky
for each row execute procedure deepsky_nodups();

Here's my insert attempt.

astro=# insert into deepsky (catalog, entry) values ('NGC', '1');
NOTICE: plpgsql: ERROR during compile of deepsky_nodups near line 10
ERROR: parse error at or near "if"

Note that I removed a few clauses. It barfs at the end of the main if
(...) clause. I'm not sure if it is telling me there is something
generic wrong or if it doesn't like the final "end if".

Basically, if I have *any* elsif clauses, it fails. If I have only
the main "if" and an "else" clause it works.

roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland(at)rlenter(dot)com 76-15 113th Street, Apt 3B
roland(at)astrofoto(dot)org Forest Hills, NY 11375