Re: plpgsql trigger parse error - Mailing list pgsql-general

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