pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Why 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