I'm writing a tool for web-based management of DNS records, and I've
come up against a UI nuisance that I'm hoping I can get solved in
Postgres instead of some higher layer.
One table contains all of the live records:
CREATE TABLE records (
domain_id integer NOT NULL DEFAULT 0,
rdns_id integer NOT NULL DEFAULT 0,
record_id serial NOT NULL,
host text DEFAULT '' NOT NULL,
"type" integer DEFAULT 1 NOT NULL,
val text DEFAULT '' NOT NULL,
distance integer DEFAULT 0 NOT NULL,
weight integer DEFAULT 0 NOT NULL,
port integer DEFAULT 0 NOT NULL,
ttl integer DEFAULT 7200 NOT NULL,
description text
);
host is the hostname, val is the target or result for forward zones
For reverse zones, val is the IP (strictly speaking, the ip6.arpa or
in-addr.arpa "hostname", stored as an IP address and converted on
export), and host is the resulting hostname.
For reverse zones I can simply sort on CAST(val AS inet), since val
should never be anything other than a valid IP or CIDR.
For forward zones, though, I can't just unconditionally cast the column
as inet, because there are all kinds of values that are not valid IP or
CIDR addresses. I still want to sort the IPs in this field properly
though; eg, 192.168.1.100 should come just after 192.168.1.99, not
192.168.1.10.
Is there any way to conditionally cast a value for sorting? I don't
care if IP addresses end up in a big block at the beginning or end of
the list so long as it's consistent.
-kgd
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
