Re: how would I do this in a stored procedure? (solved)

It took a while but I managed to get my ass in the PL/pgSQL seat long enough to figure out howto do it.

Thanks all!

Eric
----- Original Message -----
From: Eric Peters
To: pgsql-general(at)postgresql(dot)org
Sent: Saturday, February 09, 2002 7:35 PM
Subject: [GENERAL] how would I do this in a stored procedure?

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