Re: Longest prefix matching CTE

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(at)gmail(dot)com>:

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