{"id":78,"date":"2015-05-21T07:03:23","date_gmt":"2015-05-20T21:03:23","guid":{"rendered":"https:\/\/www.neuralglue.com\/?p=78"},"modified":"2015-05-22T00:44:26","modified_gmt":"2015-05-21T14:44:26","slug":"custom-functions-in-flask-sqlalchemy-with-postgresql","status":"publish","type":"post","link":"https:\/\/www.neuralglue.com\/?p=78","title":{"rendered":"Custom Functions in Flask-SQLAlchemy with PostgreSQL"},"content":{"rendered":"

I have a Flask-SQLAlchemy\u00c2\u00a0model backed by a PostgreSQL database that looks like this:<\/p>\n

class Thing(db.Model)<\/code><\/p>\n

title = db.Column(db.Text(), nullable=False)
\nnarrative = db.Column(db.Text(), nullable=False)
\ntags = db.Column(ARRAY(db.Text()), index=True)
\n<\/code><\/p>\n

My customer wants to have a full text search over all three fields simultaneously, but wants the results ordered by where the hits are. They should be ordered first by tag, then title, then narrative.<\/p>\n

This is a job for PostgreSQL full text search<\/a>. So first I’ll need to create a ‘document’ by concatenating the fields and then search across the set of documents\u00c2\u00a0for my search string.<\/p>\n

Unfortunately it’s not that simple, as the\u00c2\u00a0tags type is a text array. array_to_string is not immutable, since it is dependent on locale, so we will need to provide an immutable convert function.<\/p>\n

If I were doing this directly in the database I’d need to do something like this:<\/p>\n

CREATE OR REPLACE FUNCTION tags_to_string(text[])\r\nRETURNS text\r\nAS\r\n$BODY$\r\n select array_to_string($1, ' ');\r\n$BODY$\r\nLANGUAGE sql\r\nIMMUTABLE;<\/pre>\n
Then, we can create a simple three column text search vector<\/a>\u00c2\u00a0simply by:<\/p>\n
CREATE OR REPLACE FUNCTION\r\nthree_column_ts_vector(text, text, text)\r\nRETURNS tsvector\r\nAS\r\n$BODY$\r\n select ( $1 || '':1A '' || $2 || '':1B '' || $3 || '':1C'' )::tsvector;\r\n$BODY$\r\nLANGUAGE sql\r\nSTRICT \r\nIMMUTABLE;<\/pre>\n
And, of course, we want an index on the documents to make our searches speedy:<\/p>\n
CREATE INDEX ON thing USING gin( three_column_ts_vector(tags_to_string(tags), title, narrative) );<\/pre>\n
So, how do we get Flask_SQLAlchemy to generate the function and how do we use it in our index creation?<\/p>\n
I use the Declarative model, so we can’t just grab the metadata object and pass execute statements to it. instead, I use listeners to listen for table creation:<\/p>\n
create_function_tags_to_string = DDL(\"CREATE OR REPLACE FUNCTION tags_to_string(text[]) RETURNS text AS $BODY$ select array_to_string($1, ' '); $BODY$ LANGUAGE sql IMMUTABLE;\")\r\ncreate_function_three_column_ts_vector = DDL(\"create or replace function three_column_ts_vector(text, text, text) returns tsvector strict immutable language sql as 'select ( $1 || '':1A '' || $2 || '':1B '' || $3 || '':1C'' )::tsvector;';\")\r\ncreate_composite_index = DDL(\"create index on things using gin( three_column_ts_vector(tags_to_string(tags), title, narrative) );\")<\/pre>\n
event.listen(Thing.__table__, 'after_create', create_function_tags_to_string.execute_if(dialect='postgresql'))\r\nevent.listen(Thing.__table__, 'after_create', create_function_three_column_ts_vector.execute_if(dialect='postgresql'))\r\nevent.listen(Thing.__table__, 'after_create', create_composite_index.execute_if(dialect='postgresql'))<\/pre>\n
Note that this is only triggered by the initial creation of the table. You may wish to add listeners to other events to capture alter tables or drops.<\/p>\n
An alternative might be to add this to your migrate scripts – but it means you’re maintaining model attributes outside your model classes, which is fraught with potentially hard to find bugs.<\/p>\n","protected":false},"excerpt":{"rendered":"
I have a Flask-SQLAlchemy\u00c2\u00a0model backed by a PostgreSQL database that looks like […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[13],"tags":[],"_links":{"self":[{"href":"https:\/\/www.neuralglue.com\/index.php?rest_route=\/wp\/v2\/posts\/78"}],"collection":[{"href":"https:\/\/www.neuralglue.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.neuralglue.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.neuralglue.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.neuralglue.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=78"}],"version-history":[{"count":3,"href":"https:\/\/www.neuralglue.com\/index.php?rest_route=\/wp\/v2\/posts\/78\/revisions"}],"predecessor-version":[{"id":81,"href":"https:\/\/www.neuralglue.com\/index.php?rest_route=\/wp\/v2\/posts\/78\/revisions\/81"}],"wp:attachment":[{"href":"https:\/\/www.neuralglue.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=78"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.neuralglue.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=78"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.neuralglue.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=78"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}