Re: Longest prefix matching CTE

> On 25 Feb 2015, at 24:50, Tim Smith <randomdev4+postgres(at)gmail(dot)com> wrote:
>
> 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
> /

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

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

The CTE would look something like this, assuming that :x is some parameter from outside the query ($1 here):

with recursive x(level) as (
select $1 as phone, to_number(substr($1, 1, length($1))) as pfx, length($1 ) as len, 1 as level
union all
select $1 as phone, to_number(substr($1, 1, length($1)-level+1 )) as pfx, length($1 ) -level+1 as len, level +1 as level
from x
where level <= x.len
)
select * from x;

Or:
select $1 as phone, to_number(substr($1, 1, length($1) - pos as pfx, length($1) as len
from generate_series(0, length($1)-1)(x);

BTW, I didn't test any of these (I'm late already!).

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.