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
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 |