Thread: How would I make this into a stored procedure?

----- Original Message -----

From: Eric Peters

To: pgsql-novice@postgresql.org

Sent: Sunday, February 10, 2002 9:20 AM

Subject: [NOVICE] How would I make this into 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