pg-sphinx

Pg-sphinx is an extension for PostgreSQL which allows to integrate Sphinx search engine.

Installation

Compile

make

Install

sudo make install

Define sphinx functions in your database

Superuser is required.

echo 'CREATE EXTENSION sphinx;' | psql -U postgres mydatabase

Uninstall

sudo make uninstall

Configuration

Extension can be configured by modifying corresponding rows in table sphinx_config. Following options are available: ‘host’, ‘post’, ‘username’, ‘password’, ‘prefix’.

‘Prefix’ is a string which is prepended to index names. This option is useful to simulate namespaces. For example, if prefix is ‘test_’ and index name in a request is ‘blog_posts’, real request will be addressed to index named ‘test_blog_posts’.

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'