Re: Longest prefix matching CTE

Will take a look. Thanks steve.

On 24 February 2015 at 23:57, Steve Atkins <steve(at)blighty(dot)com> wrote:
>
> 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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general