From: | Kris Deugau <kdeugau(at)vianet(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Conditional cast for eg sorting? |
Date: | 2012-06-21 15:45:21 |
Message-ID: | 4FE34191.7070907@vianet.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-06-21 16:05:24 | Re: (BUG ?) unprefixed oid -> ERROR: cache lookup failed for function |
Previous Message | David Pirotte | 2012-06-21 15:10:28 | Promoting sync slave to master without incrementing timeline counter? |