pg-sphinx

Pg-sphinx is a module for PostgreSQL which allows to integrate Sphinx search engine.

Installation

Note for non-linux users

GNU libtool is used to build this software. It’s possible you need to define LIBTOOL variable.

export LIBTOOL=glibtool

Compile

make

Install

sudo make install

Define sphinx functions in your database

Superuser is required.

psql -U postgres mydatabase < declare.sql

Uninstall

sudo make uninstall

Functions

Search query

sphinx_select(
    /*index*/     varchar,
    /*query*/     varchar,
    /*condition*/ varchar,
    /*order*/     varchar,
    /*offset*/    int,
    /*limit*/     int,
    /*options*/   varchar)

Returns pairs (id, weight).

Update data

sphinx_replace(
    /*index*/     varchar,
    /*id*/        int,
    /*data*/      varchar[])

Updates document with specified id. Data array must have following format: ARRAY[‘key1’, ‘value2’, …]

Delete data

sphinx_delete(
    /*index*/     varchar,
    /*id*/        int)

Removes specified document.

Get snippet

sphinx_snippet(
    /*index*/     varchar,
    /*query*/     varchar,
    /*data*/      varchar,
    /*before*/    varchar,
    /*after*/     varchar)

Returns snippets for a given data and search query.

Example:

SELECT sphinx_snippet('blog_posts', 'photo', 'There are photos from monday meeting', '<b>', '</b>')

This query will return following text:

'There are <b>photos</b> from monday meeting'