Postgres Pro
Downloads
Home   >   mailing lists

How would I make this into a stored procedure? - Mailing list pgsql-novice

From Eric Peters
Subject How would I make this into a stored procedure?
Date
Msg-id 002301c1b257$3c5e0ca0$4500a8c0@er1c
Whole thread Raw
List pgsql-novice
Tree view
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

pgsql-novice by date:

Previous
From: "Sykora, Dale"
Date:
Subject: documentation problem
Next
From: "Eric Peters"
Date:
Subject: Re: How would I make this into a stored procedure? (Solved)