Re: Longest prefix matching CTE

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Some other solutions

http://postgres.cz/wiki/PostgreSQL_SQL_Tricks_II#Fast_searching_of_longer_prefix

2015-02-25 9:04 GMT+01:00 Tim Smith <randomdev4+postgres@xxxxxxxxx>:

Will take a look.  Thanks steve.

On 24 February 2015 at 23:57, Steve Atkins <steve@xxxxxxxxxxx> wrote:
>
> On Feb 24, 2015, at 3:50 PM, Tim Smith <randomdev4+postgres@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general