Conditional cast for eg sorting?

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

Responses

Browse pgsql-general by date

  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?