fts.sql

-- Simple plpython function performing a full text search
CREATE OR REPLACE FUNCTION fts(sterm text) RETURNS SETOF text
AS $$
    sterms = plpy.quote_literal(sterm)
    words = " & ".join(sterms.split(" "))
    res = plpy.execute("SELECT title FROM articles WHERE to_tsvector('english',title || ' ' ||  abstract) @@ to_tsquery('english', %s)" % words)
    return [ (r["title"]) for r in res]
$$ LANGUAGE plpythonu;

-- dummy database of NYTimes articles (via their API)
/*
fts=# select fts('senators');
                                fts                                 
--------------------------------------------------------------------
 Republicans Back Down, Ending Crisis Over Shutdown and Debt Limit
 Senators Restart Talks as Default Looms
 Senators Near Fiscal Deal, but the House Is Uncertain
 A Wife Committed to Cruz’s Ideals, but a Study in Contrasts to Him
(4 rows)
*/