Here are the tables:
CREATE TABLE "zone_host" (
"zone_host_id" int4 DEFAULT nextval('"zone_host_id_seq"'::text) NOT NULL,
"zone_fqdn" varchar(255) NOT NULL,
CONSTRAINT "zone_host_pkey" PRIMARY KEY ("zone_host_id")
);
CREATE UNIQUE INDEX "zone_host_zone_fqdn_key" ON "zone_host" ("zone_fqdn");
CREATE TABLE "zone" (
"zone_id" int4 DEFAULT nextval('"zone_id_seq"'::text) NOT NULL,
"zone_host_id" int4 NOT NULL,
"controlling_contact_id" int4 NOT NULL,
CONSTRAINT "zone_pkey" PRIMARY KEY ("zone_id")
);
CREATE UNIQUE INDEX "zone_zone_host_id_key" ON "zone" ("zone_host_id");
CREATE TABLE "zone_dns" (
"zone_dns_id" int4 DEFAULT nextval('"zone_dns_id_seq"'::text) NOT NULL,
"zone_dns_updated" timestamp NOT NULL,
"zone_dns_type" varchar(8),
"zone_host_id" int4 NOT NULL,
"zone_dns_ip" varchar(15),
"zone_dns_target" varchar(255),
"zone_dns_mx_dist" int2,
"zone_dns_ttl" int4,
"zone_dns_ttl_timestamp" timestamp NOT NULL,
"zone_dns_serial" int4,
"zone_dns_refresh" int4,
"zone_dns_retry" int4,
"zone_dns_expire" int4,
"zone_dns_minimum" int4,
"zone_dns_parentid" int4 NOT NULL,
CONSTRAINT "zone_dns_pkey" PRIMARY KEY ("zone_dns_id"),
CONSTRAINT "zone_dns_zone_dns_type" CHECK (((((((((zone_dns_type = '+'::"varchar") OR (zone_dns_type = 'C'::"varchar")) OR (zone_dns_type = '='::"varchar")) OR (zone_dns_type = '@'::"varchar")) OR (zone_dns_type = '&'::"varchar")) OR (zone_dns_type = '.'::"varchar")) OR (zone_dns_type = '^'::"varchar")) OR (zone_dns_type = 'Z'::"varchar")) OR NULL::bool)
);
What I want done in a stored procedure:
input: a zone_fqdn
select
zone_dns_id,
zone_dns.zone_host_id,
zone_dns_parent_id
from
zone_host,
zone_dns
where
zone_host.zone_fqdn = 'input: zone_fqdn' and
zone_dns.zone_host_id = zone_host.zone_host_id
If the returned zone_dns_parent_id != 0:
input: zone_dns.zone_parent_id
select
zone_dns_id,
zone_host_id,
zone_dns_parent_id
from
zone_dns
where
zone_dns_id = 'input: zone_dns.zone_parent_id'
If zone_dns_parent_id != 0 Loop until we have a zone_dns_parent_id == 0
After we have a "good" zone_dns entry which is the "highest level,"
input: zone_dns.zone_host_id
select
controlling_contact_id
from
zone
where
zone_host_id = 'input: zone_dns.zone_host_id'
The entire procedure then returns the controlling_contact_id
Thanks!
Eric
