Longest prefix matching CTE

From: Tim Smith <randomdev4+postgres(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Longest prefix matching CTE
Date: 2015-02-24 23:50:33
Message-ID: CA+HuS5GygtUMvc5cbX5Rge_x77CcUTAKYzcKqSuKETbaySztgg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Have an Oracle "connect by" SQL that looks something like :

select phone, pfx, len, (select info from codes where
pfx = x.pfx) infot
from (
select :x phone, to_number(substr( :x, 1, length(:x)-level+1 )) pfx,
length(:x)-level+1 len
from dual
connect by level <= length(:x)
order by level
) x
where rownum = 1
and (select info from codes where pfx = x.pfx) is not null
/

Where codes is essentially a two column table :

create table codes(pfx bigint,info text);

And its contents look like :

61882 Australia - Sydney
61883 Australia - Sydney
61884 Australia - Sydney
61892 Australia - Sydney
61893 Australia - Sydney
61894 Australia - Sydney
6113 Australia - Premium
6118 Australia - Premium
61 Australia - Proper

The goal being to match the longest prefix given a full phone number, e.g.

61234567890 would match "australia proper 61"
whilst
61134567890 would match "Australia premium 6113"
and
61894321010 would match "Australia - Sydney 61893"

I know the answer involves Postgres CTE, but I haven't used CTEs much
yet... let alone in complex queries such as this.

Thanking you all in advance for your kind help.

T

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Atkins 2015-02-24 23:57:47 Re: Longest prefix matching CTE
Previous Message Dowwie 2015-02-24 23:32:28 Re: Row-level Security vs Application-level authz