Thread: Why doesn't this pgsql function compile?
When this PLpgsql function gets called on the trigger
it fails compilation on the last line.
What am I doing wrong?
DROP TRIGGER netblock_bgp ON networks;
DROP FUNCTION networks_trigger_router();
CREATE FUNCTION networks_trigger_router()
RETURNS opaque
AS 'BEGIN IF TG_OP = ''DELETE'' AND OLD.router ISNULL THEN return OLD; END IF; IF TG_OP = ''INSERT'' AND
NEW.routerISNULL THEN return NEW; END IF; IF TG_OP = ''INSERT'' AND get_router_nexthop(NEW.router) ISNULL THEN
returnNEW; END IF; IF TG_OP = ''DELETE'' AND get_router_nexthop(OLD.router) ISNULL THEN return OLD; END IF;
IFTG_OP = ''INSERT'' THEN add_ip_route(host(network(NEW.netblock)), host(netmask(NEW.netblock)),
get_router_ethernet(NEW.router), get_router_nexthop(NEW.router),
get_router_nexthop_user(NEW.router), get_router_nexthop_password(NEW.router),
get_router_nexthop_enpass(NEW,router)); return NEW; END IF; IF TG_OP = ''DELETE'' THEN
del_ip_route(host(network(OLD.netblock)), host(netmask(OLD.netblock)),
get_router_ethernet(OLD.router), get_router_nexthop(OLD.router),
get_router_nexthop_user(OLD.router), get_router_nexthop_password(OLD.router),
get_router_nexthop_enpass(OLD,router)); return OLD; -- TG_OP must be UPDATE to get here.... IF OLD.router
NOTNULLand get_router_nexthop(OLD.router) NOTNULL AND NEW.router NOTNULL and OLD.router != NEW.router THEN
del_ip_route(host(network(OLD.netblock)), host(netmask(OLD.netblock)),
get_router_ethernet(OLD.router), get_router_nexthop(OLD.router),
get_router_nexthop_user(OLD.router), get_router_nexthop_password(OLD.router),
get_router_nexthop_enpass(OLD,router)); END IF; IF NEW.router NOTNULL and get_router_nexthop(NEW.router) NOTNULL
THEN add_ip_route(host(network(NEW.netblock)), host(netmask(NEW.netblock)),
get_router_ethernet(NEW.router), get_router_nexthop(NEW.router),
get_router_nexthop_user(NEW.router), get_router_nexthop_password(NEW.router),
get_router_nexthop_enpass(NEW,router)); END IF; return NEW; END;'
LANGUAGE 'plpgsql';
CREATE TRIGGER netblock_bgp
BEFORE UPDATE OR INSERT OR DELETE
ON networks
FOR EACH ROW
EXECUTE PROCEDURE networks_trigger_router();
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
On Fri, 15 Jun 2001, Larry Rosenman wrote: <snip> you are missing an endif: > DROP TRIGGER netblock_bgp ON networks; > DROP FUNCTION networks_trigger_router(); > CREATE FUNCTION networks_trigger_router() > RETURNS opaque > AS 'BEGIN > IF TG_OP = ''DELETE'' AND OLD.router ISNULL > THEN return OLD; > END IF; > IF TG_OP = ''INSERT'' AND NEW.router ISNULL > THEN return NEW; > END IF; > IF TG_OP = ''INSERT'' AND get_router_nexthop(NEW.router) ISNULL > THEN return NEW; > END IF; > IF TG_OP = ''DELETE'' AND get_router_nexthop(OLD.router) ISNULL > THEN return OLD; > END IF; > IF TG_OP = ''INSERT'' > THEN add_ip_route(host(network(NEW.netblock)), > host(netmask(NEW.netblock)), > get_router_ethernet(NEW.router), > get_router_nexthop(NEW.router), > get_router_nexthop_user(NEW.router), > get_router_nexthop_password(NEW.router), > get_router_nexthop_enpass(NEW,router)); > return NEW; > END IF; > IF TG_OP = ''DELETE'' > THEN del_ip_route(host(network(OLD.netblock)), > host(netmask(OLD.netblock)), > get_router_ethernet(OLD.router), > get_router_nexthop(OLD.router), > get_router_nexthop_user(OLD.router), > get_router_nexthop_password(OLD.router), > get_router_nexthop_enpass(OLD,router)); > return OLD; ^^^^^^^^^^^^^^^^ END IF; > -- TG_OP must be UPDATE to get here.... > IF OLD.router NOTNULL and get_router_nexthop(OLD.router) NOTNULL > AND NEW.router NOTNULL and OLD.router != NEW.router > THEN del_ip_route(host(network(OLD.netblock)), > host(netmask(OLD.netblock)), > get_router_ethernet(OLD.router), > get_router_nexthop(OLD.router), > get_router_nexthop_user(OLD.router), > get_router_nexthop_password(OLD.router), > get_router_nexthop_enpass(OLD,router)); > END IF; > IF NEW.router NOTNULL and get_router_nexthop(NEW.router) NOTNULL > THEN add_ip_route(host(network(NEW.netblock)), > host(netmask(NEW.netblock)), > get_router_ethernet(NEW.router), > get_router_nexthop(NEW.router), > get_router_nexthop_user(NEW.router), > get_router_nexthop_password(NEW.router), > get_router_nexthop_enpass(NEW,router)); > END IF; > return NEW; > END;' > LANGUAGE 'plpgsql'; > CREATE TRIGGER netblock_bgp > BEFORE UPDATE OR INSERT OR DELETE > ON networks > FOR EACH ROW > EXECUTE PROCEDURE networks_trigger_router(); > >
Yes, I found that out, as well as needing to save off the new. and old. var's... :-) LER * Joe Conway <joseph.conway@home.com> [010615 17:43]: > > > When this PLpgsql function gets called on the trigger > > it fails compilation on the last line. > > > > What am I doing wrong? > > > > > > <snip> > > > IF TG_OP = ''INSERT'' > > THEN add_ip_route(host(network(NEW.netblock)), > > host(netmask(NEW.netblock)), > > get_router_ethernet(NEW.router), > > get_router_nexthop(NEW.router), > > get_router_nexthop_user(NEW.router), > > get_router_nexthop_password(NEW.router), > > get_router_nexthop_enpass(NEW,router)); > > return NEW; > > END IF; > > Don't you need a "PERFORM" between the "THEN" and "add_ip_route(. . ." above > (and similar places below)? > > -- Joe > > > IF TG_OP = ''DELETE'' > > THEN del_ip_route(host(network(OLD.netblock)), > > host(netmask(OLD.netblock)), > > get_router_ethernet(OLD.router), > > get_router_nexthop(OLD.router), > > get_router_nexthop_user(OLD.router), > > get_router_nexthop_password(OLD.router), > > get_router_nexthop_enpass(OLD,router)); > > return OLD; > > -- TG_OP must be UPDATE to get here.... > > IF OLD.router NOTNULL and get_router_nexthop(OLD.router) NOTNULL > > AND NEW.router NOTNULL and OLD.router != NEW.router > > THEN del_ip_route(host(network(OLD.netblock)), > > host(netmask(OLD.netblock)), > > get_router_ethernet(OLD.router), > > get_router_nexthop(OLD.router), > > get_router_nexthop_user(OLD.router), > > get_router_nexthop_password(OLD.router), > > get_router_nexthop_enpass(OLD,router)); > > END IF; > > IF NEW.router NOTNULL and get_router_nexthop(NEW.router) NOTNULL > > THEN add_ip_route(host(network(NEW.netblock)), > > host(netmask(NEW.netblock)), > > get_router_ethernet(NEW.router), > > get_router_nexthop(NEW.router), > > get_router_nexthop_user(NEW.router), > > get_router_nexthop_password(NEW.router), > > get_router_nexthop_enpass(NEW,router)); > > END IF; > > return NEW; > > END;' > > LANGUAGE 'plpgsql'; > >
> When this PLpgsql function gets called on the trigger > it fails compilation on the last line. > > What am I doing wrong? > > <snip> > IF TG_OP = ''INSERT'' > THEN add_ip_route(host(network(NEW.netblock)), > host(netmask(NEW.netblock)), > get_router_ethernet(NEW.router), > get_router_nexthop(NEW.router), > get_router_nexthop_user(NEW.router), > get_router_nexthop_password(NEW.router), > get_router_nexthop_enpass(NEW,router)); > return NEW; > END IF; Don't you need a "PERFORM" between the "THEN" and "add_ip_route(. . ." above (and similar places below)? -- Joe > IF TG_OP = ''DELETE'' > THEN del_ip_route(host(network(OLD.netblock)), > host(netmask(OLD.netblock)), > get_router_ethernet(OLD.router), > get_router_nexthop(OLD.router), > get_router_nexthop_user(OLD.router), > get_router_nexthop_password(OLD.router), > get_router_nexthop_enpass(OLD,router)); > return OLD; > -- TG_OP must be UPDATE to get here.... > IF OLD.router NOTNULL and get_router_nexthop(OLD.router) NOTNULL > AND NEW.router NOTNULL and OLD.router != NEW.router > THEN del_ip_route(host(network(OLD.netblock)), > host(netmask(OLD.netblock)), > get_router_ethernet(OLD.router), > get_router_nexthop(OLD.router), > get_router_nexthop_user(OLD.router), > get_router_nexthop_password(OLD.router), > get_router_nexthop_enpass(OLD,router)); > END IF; > IF NEW.router NOTNULL and get_router_nexthop(NEW.router) NOTNULL > THEN add_ip_route(host(network(NEW.netblock)), > host(netmask(NEW.netblock)), > get_router_ethernet(NEW.router), > get_router_nexthop(NEW.router), > get_router_nexthop_user(NEW.router), > get_router_nexthop_password(NEW.router), > get_router_nexthop_enpass(NEW,router)); > END IF; > return NEW; > END;' > LANGUAGE 'plpgsql';
* Larry Rosenman <ler@lerctr.org> [010615 17:47]: > > Yes, I found that out, as well as needing to save off > the new. and old. var's... > > :-) BTW, thanks, guys..... Too much staring at code... I *REALLY* appreciate the good eyes... It's now working! :-) (my first set of triggers), and it even talks to the routers... PeterE: Thanks for PL/sh, I'm using it to launch a couple of python scripts to actually do the router talking and mail us. LER > > LER > > * Joe Conway <joseph.conway@home.com> [010615 17:43]: > > > > > When this PLpgsql function gets called on the trigger > > > it fails compilation on the last line. > > > > > > What am I doing wrong? > > > > > > > > > > <snip> > > > > > IF TG_OP = ''INSERT'' > > > THEN add_ip_route(host(network(NEW.netblock)), > > > host(netmask(NEW.netblock)), > > > get_router_ethernet(NEW.router), > > > get_router_nexthop(NEW.router), > > > get_router_nexthop_user(NEW.router), > > > get_router_nexthop_password(NEW.router), > > > get_router_nexthop_enpass(NEW,router)); > > > return NEW; > > > END IF; > > > > Don't you need a "PERFORM" between the "THEN" and "add_ip_route(. . ." above > > (and similar places below)? > > > > -- Joe > > > > > IF TG_OP = ''DELETE'' > > > THEN del_ip_route(host(network(OLD.netblock)), > > > host(netmask(OLD.netblock)), > > > get_router_ethernet(OLD.router), > > > get_router_nexthop(OLD.router), > > > get_router_nexthop_user(OLD.router), > > > get_router_nexthop_password(OLD.router), > > > get_router_nexthop_enpass(OLD,router)); > > > return OLD; > > > -- TG_OP must be UPDATE to get here.... > > > IF OLD.router NOTNULL and get_router_nexthop(OLD.router) NOTNULL > > > AND NEW.router NOTNULL and OLD.router != NEW.router > > > THEN del_ip_route(host(network(OLD.netblock)), > > > host(netmask(OLD.netblock)), > > > get_router_ethernet(OLD.router), > > > get_router_nexthop(OLD.router), > > > get_router_nexthop_user(OLD.router), > > > get_router_nexthop_password(OLD.router), > > > get_router_nexthop_enpass(OLD,router)); > > > END IF; > > > IF NEW.router NOTNULL and get_router_nexthop(NEW.router) NOTNULL > > > THEN add_ip_route(host(network(NEW.netblock)), > > > host(netmask(NEW.netblock)), > > > get_router_ethernet(NEW.router), > > > get_router_nexthop(NEW.router), > > > get_router_nexthop_user(NEW.router), > > > get_router_nexthop_password(NEW.router), > > > get_router_nexthop_enpass(NEW,router)); > > > END IF; > > > return NEW; > > > END;' > > > LANGUAGE 'plpgsql'; > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749