pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy doesn't this pgsql function compile?
5+ messages / 3 participants
[nested] [flat]
* Why doesn't this pgsql function compile?
@ 2001-06-15 22:09 Larry Rosenman <ler@lerctr.org>
0 siblings, 2 replies; 5+ messages in thread
From: Larry Rosenman @ 2001-06-15 22:09 UTC (permalink / raw)
To: pgsql-sql
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.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;
-- 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();
--
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why doesn't this pgsql function compile?
@ 2001-06-15 22:27 Alex Pilosov <alex@pilosoft.com>
parent: Larry Rosenman <ler@lerctr.org>
1 sibling, 0 replies; 5+ messages in thread
From: Alex Pilosov @ 2001-06-15 22:27 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: pgsql-sql
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();
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why doesn't this pgsql function compile?
@ 2001-06-15 22:42 Joe Conway <joseph.conway@home.com>
parent: Larry Rosenman <ler@lerctr.org>
1 sibling, 1 reply; 5+ messages in thread
From: Joe Conway @ 2001-06-15 22:42 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; pgsql-sql
> 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';
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why doesn't this pgsql function compile?
@ 2001-06-15 22:43 Larry Rosenman <ler@lerctr.org>
parent: Joe Conway <joseph.conway@home.com>
0 siblings, 1 reply; 5+ messages in thread
From: Larry Rosenman @ 2001-06-15 22:43 UTC (permalink / raw)
To: Joe Conway <joseph.conway@home.com>; +Cc: pgsql-sql
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';
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why doesn't this pgsql function compile?
@ 2001-06-16 03:36 Larry Rosenman <ler@lerctr.org>
parent: Larry Rosenman <ler@lerctr.org>
0 siblings, 0 replies; 5+ messages in thread
From: Larry Rosenman @ 2001-06-16 03:36 UTC (permalink / raw)
To: Joe Conway <joseph.conway@home.com>; +Cc: pgsql-sql
* 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-06-16 03:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-15 22:09 Why doesn't this pgsql function compile? Larry Rosenman <ler@lerctr.org>
2001-06-15 22:27 ` Alex Pilosov <alex@pilosoft.com>
2001-06-15 22:42 ` Joe Conway <joseph.conway@home.com>
2001-06-15 22:43 ` Larry Rosenman <ler@lerctr.org>
2001-06-16 03:36 ` Larry Rosenman <ler@lerctr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox