Conditional cast for eg sorting?

[Kris Deugau <kdeugau@xxxxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general