Re: plpgsql trigger parse error

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 and component 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.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();
>
> 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 component 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 and component 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.suffix and 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.component and 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.component and 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(at)rlenter(dot)com 76-15 113th Street, Apt 3B
> roland(at)astrofoto(dot)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(at)postgresql(dot)org)
>

--
Darren Ferguson