using tsvector

[Gery Herbozo]

Hello,

I have a question about tsvector used as a type in GeoAlchemy. I've
been looking for a way to use tsvector (simply like other ones such as
INTEGER, etc), but so far it's not clear to me how to do this. I've
read that tsvector can be implemented as a type using UserDefinedType.
After some attempts I'm getting nowhere, someone has a simple way to
do this? I'd appreciate if someone could share some examples with me
about using this.

Thanks in advance, best regards,

[Eric Lemoine]

It isn't a GeoAlchemy question.

This is what we have been using here:

class TsVector(types.UserDefinedType):
""" A custom type for PostgreSQL's tsvector type. """
def get_col_spec(self):
return 'TSVECTOR'


class FullTextSearch(GeoInterface, Base):
__tablename__ = 'tsearch'
__table_args__ = (
Index('tsearch_ts_idx', 'ts', postgresql_using='gin'),
{'schema': _schema}
)
id = Column('id', types.Integer, primary_key=True)
ts = Column('ts', TsVector)
the_geom = GeometryColumn(Geometry(srid=_srid))



--
Eric

[Gery Herbozo]

thanks a lot Eric, it works nicely.

> class TsVector(types.UserDefinedType):
> """ A custom type for PostgreSQL's tsvector type. """
> def get_col_spec(self):
> return 'TSVECTOR'
>
>
> class FullTextSearch(GeoInterface, Base):
> __tablename__ = 'tsearch'
> __table_args__ = (
> Index('tsearch_ts_idx', 'ts', postgresql_using='gin'),
> {'schema': _schema}
> )
> id = Column('id', types.Integer, primary_key=True)
> ts = Column('ts', TsVector)
> the_geom = GeometryColumn(Geometry(srid=_srid))

based on this example, how could I do a simple query using this ts? in
postgresql it should be like this: select * from tsearch where ts @@
to_tsquery('something');

I think this could work but it says ts is not defined:
ts.op('@@@')(func.to_tsvector("something"))

thanks,

[Eric Lemoine]

We use text here:

_filter = "%(tsvector)s @@ to_tsquery('%(lang)s', '%(terms)s')" % \
{'tsvector': 'ts', 'lang': lang, 'terms': terms}
query = DBSession.query(FullTextSearch).filter(_filter)


There may be better ways.





--
Eric

[Gery Herbozo]

thanks a world Eric, this works also:
session.query(Model).filter(Model.tsvector.op('@@')(func.plainto_tsquery('search
string')))