Thread: plpgsql trigger parse error
I am having trouble getting a pl/PgSQL function to parse correctly and
my usual divide and conquer strategy is not working very well. The
full function appears at the bottom. When I simplified it to only the
first if (...) clause, it works. When I added the first else if (...)
clause, as shown immediately below, it fails.
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
componentis null)) then
return NULL;
end if;
else if (NEW.component is NULL) then
if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and suffix =
NEW.suffixand 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();
When it fires via the following insert
insert into deepsky (catalog, entry) values ('NGC', '1');
I get this error
NOTICE: plpgsql: ERROR during compile of deepsky_nodups near line 6
ERROR: parse error at or near "else"
I've stared at this so long I've run out of ideas on what might be
wrong. I started by cutting it down to the first if (...) and exists
clause. That works fine, i.e.,
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
componentis null)) then
return NULL;
end if;
end if;
return NEW;
end;
' language 'plpgsql';
is fine. The full function gives me a different error,
NOTICE: plpgsql: ERROR during compile of deepsky_nodups near line 20
ERROR: parse error at or near ";"
Here is the full function:
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
componentis null)) then
return NULL;
end if;
else if (NEW.component is NULL) then
if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and suffix =
NEW.suffixand component is null)) then
return NULL;
end if;
else if (NEW.suffix is NULL) then
if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and component =
NEW.componentand suffix is null)) then
return NULL;
end if;
else
if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and component =
NEW.componentand suffix = NEW.suffix)) then
return NULL;
end if;
end if;
return NEW;
end;
' language 'plpgsql';
What am I missing?
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
Check the plpgsql manual for else if you will not it as the following
elsif
HTH
On 11 Sep 2002, Roland Roberts wrote:
> I am having trouble getting a pl/PgSQL function to parse correctly and
> my usual divide and conquer strategy is not working very well. The
> full function appears at the bottom. When I simplified it to only the
> first if (...) clause, it works. When I added the first else if (...)
> clause, as shown immediately below, it fails.
>
> 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
andcomponent is null)) then
> return NULL;
> end if;
> else if (NEW.component is NULL) then
> if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and suffix =
NEW.suffixand 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();
>
> When it fires via the following insert
>
> insert into deepsky (catalog, entry) values ('NGC', '1');
>
> I get this error
>
> NOTICE: plpgsql: ERROR during compile of deepsky_nodups near line 6
> ERROR: parse error at or near "else"
>
> I've stared at this so long I've run out of ideas on what might be
> wrong. I started by cutting it down to the first if (...) and exists
> clause. That works fine, i.e.,
>
> 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
andcomponent is null)) then
> return NULL;
> end if;
> end if;
> return NEW;
> end;
> ' language 'plpgsql';
>
> is fine. The full function gives me a different error,
>
> NOTICE: plpgsql: ERROR during compile of deepsky_nodups near line 20
> ERROR: parse error at or near ";"
>
> Here is the full function:
>
> 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
andcomponent is null)) then
> return NULL;
> end if;
> else if (NEW.component is NULL) then
> if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and suffix =
NEW.suffixand component is null)) then
> return NULL;
> end if;
> else if (NEW.suffix is NULL) then
> if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and component =
NEW.componentand suffix is null)) then
> return NULL;
> end if;
> else
> if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and component =
NEW.componentand suffix = NEW.suffix)) then
> return NULL;
> end if;
> end if;
> return NEW;
> end;
> ' language 'plpgsql';
>
> What am I missing?
>
> roland
> --
> PGP Key ID: 66 BC 3B CD
> Roland B. Roberts, PhD RL Enterprises
> roland@rlenter.com 76-15 113th Street, Apt 3B
> roland@astrofoto.org Forest Hills, NY 11375
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
Darren Ferguson
On Wed, 2002-09-11 at 15:46, Roland Roberts wrote: > is fine. The full function gives me a different error, > > NOTICE: plpgsql: ERROR during compile of deepsky_nodups near line 20 > ERROR: parse error at or near ";" > > Here is the full function: > > 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 andcomponent is null)) then > return NULL; > end if; > else if (NEW.component is NULL) then > if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and suffix = NEW.suffixand component is null)) then > return NULL; > end if; > else if (NEW.suffix is NULL) then > if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and component = NEW.componentand suffix is null)) then > return NULL; > end if; > else > if (exists (select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and component = NEW.componentand suffix = NEW.suffix)) then > return NULL; > end if; > end if; > return NEW; > end; > ' language 'plpgsql'; > > What am I missing? Using ELSE IF requires an extra END IF; Use ELSIF instead. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me." Galatians 2:20
Thanks to everyone who pointed out my problem.
I promise to ask my next question on the novices list.... :-/
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
>>>>> "me" == Roland Roberts <roland@astrofoto.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
componentis 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.suffixand 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@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
On Wed, 2002-09-11 at 16:28, Roland Roberts wrote: > Basically, if I have *any* elsif clauses, it fails. If I have only > the main "if" and an "else" clause it works. I can't reproduce your error. In 7.2.2, it works. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me." Galatians 2:20
>>>>> "Oliver" == Oliver Elphick <olly@lfix.co.uk> writes:
Oliver> On Wed, 2002-09-11 at 16:28, Roland Roberts wrote:
>> Basically, if I have *any* elsif clauses, it fails. If I have only
>> the main "if" and an "else" clause it works.
Oliver> I can't reproduce your error. In 7.2.2, it works.
Hmmm, time to upgrade. I'm running 7.1.2.
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375