Conditional cast for eg sorting?

Поиск
Список
Период
Сортировка
От Kris Deugau
Тема Conditional cast for eg sorting?
Дата
Msg-id 4FE34191.7070907@vianet.ca
обсуждение исходный текст
Ответы Re: Conditional cast for eg sorting?  (Steve Atkins <steve@blighty.com>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: David Pirotte
Дата:
Сообщение: Promoting sync slave to master without incrementing timeline counter?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: (BUG ?) unprefixed oid -> ERROR: cache lookup failed for function