Re: Longest prefix matching CTE

On Feb 24, 2015, at 3:50 PM, Tim Smith <randomdev4+postgres(at)gmail(dot)com> wrote:

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

There's probably a CTE approach for it, but you might want to look
at https://github.com/dimitri/prefix too - it's an extension that's designed
specifically for longest prefix matching, and that uses gist indexes to
do it efficiently.

Cheers,
Steve