Hello group,

I'm playing around with triggers. What I want to achieve is a table of
subnets. All subnets should be disjoint, network is of type cidr. E.g.
The following should not be possible:

INSERT INTO subnets (network) VALUES ('192.168.1.0/24');
INSERT INTO subnets (network) VALUES ('192.168.0.0/16');

As the latter includes the former completely. It shall not be possible
in reverse order, either. Therefore, I started up with a simple type of
check (currently only checks if network is a certain parameter to try
around, later this will be replaced by the "real" check):

CREATE FUNCTION subnets_check() RETURNS TRIGGER AS
$$
BEGIN
IF NEW.network = '192.168.1.0/24'::cidr THEN
RAISE EXCEPTION 'Subnet to be inserted overlaps with a currently
existing subnet.';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER non_overlapping_subnets BEFORE INSERT ON subnets EXECUTE
PROCEDURE subnets_check();

When I do this and try to insert anything into the table, I get:

ERROR: record "new" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT: PL/pgSQL function "subnets_check" line 3 at if

It seems like it doesn't know the "NEW" keyword - but this I got from
the postgres documentation:
http://www.postgresql.org/docs/8.2/i...efunction.html

What am I doing wrong?
Thanks in advance,
Johannes

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
Change that to:

CREATE TRIGGER non_overlapping_subnets BEFORE INSERT ON subnets for each row EXECUTE PROCEDURE subnets_check();
^^^^^^^^^^^^


FOR EACH ROW


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
Change that to:

CREATE TRIGGER non_overlapping_subnets BEFORE INSERT ON subnets for each row EXECUTE PROCEDURE subnets_check();
^^^^^^^^^^^^


FOR EACH ROW


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
Change that to:

CREATE TRIGGER non_overlapping_subnets BEFORE INSERT ON subnets for each row EXECUTE PROCEDURE subnets_check();
^^^^^^^^^^^^


FOR EACH ROW


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
Change that to:

CREATE TRIGGER non_overlapping_subnets BEFORE INSERT ON subnets for each row EXECUTE PROCEDURE subnets_check();
^^^^^^^^^^^^


FOR EACH ROW


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
Change that to:

CREATE TRIGGER non_overlapping_subnets BEFORE INSERT ON subnets for each row EXECUTE PROCEDURE subnets_check();
^^^^^^^^^^^^


FOR EACH ROW


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
Change that to:

CREATE TRIGGER non_overlapping_subnets BEFORE INSERT ON subnets for each row EXECUTE PROCEDURE subnets_check();
^^^^^^^^^^^^


FOR EACH ROW


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
Change that to:

CREATE TRIGGER non_overlapping_subnets BEFORE INSERT ON subnets for each row EXECUTE PROCEDURE subnets_check();
^^^^^^^^^^^^


FOR EACH ROW


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
Change that to:

CREATE TRIGGER non_overlapping_subnets BEFORE INSERT ON subnets for each row EXECUTE PROCEDURE subnets_check();
^^^^^^^^^^^^


FOR EACH ROW


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
I'm not familiar with this data-type, but i think there are some
functions and operators for this, see:
http://www.postgresql.org/docs/curre...tions-net.html



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°