PGTSVector

[delanoe]

There are plans for adding PGTSVector Column type ?
Have you tried already ?
Do you need help ?

[tomjaguarpaw]

I suspect it would be quite similar to adding any other type and its operations, i.e. generally quite simple. Do you want to give it a go?

[delanoe]

Yes, thanks. I have found a way for now. Maybe make a PR if more useful work for others.

[delanoe]

In fact, the type of the TSVector is like this:
'string':103 'something':49 'algorithm':53

In others words:
String:Int separated with spaces

How would you type/parse it ? Thanks for the help.

[tomjaguarpaw]

Do you mean how would you create a literal of type Column PGTSVector?

[delanoe]

yes

[tomjaguarpaw]

You can add a function to do that to PGTypes.hs. Since HPQ.PrimExpr doesn't yet have a constructor to handle this data type just use HPQ.OtherLit.

[delanoe]

Thanks.

But I need the "@@" too that is not in the Internal operators.

I have found how it would be done with Haskelldb: https://chrisdone.com/posts/haskelldb-tutorial

Or maybe we want to use full text search support from PostgreSQL. Let’s add a field to represent the ts
_vector, and define a table with the searchable stuff:

-- | Search fields.
field "Textsearchable" "textsearchable" "textsearchable_index_col"
[t|TSVector|]

-- | Content table with searchable full text field.
table "contentSearchable" "content"
['id
,'title
,'textsearchable
]

Now we can redefine getContent which matches on the ts_vector:

getContentFullText q = do
article <- table T.contentSearchable
restrict $ article!F.textsearchable .@@. (to_tsquery (val q))
order [descExpr $ ts_rank_cd (article!F.textsearchable)
(to_tsquery (val q))]
return article

This can be achieved by a phantom type (i.e. an uninhabitable type at the value-level):

-- | A text-search vector.
data TSVector

-- | Convert a string to a textsearch vector.
to_tsvector :: Expr String -> Expr TSVector
to_tsvector = func "to_tsvector"

And the function can be used, at the SQL-level, because the vector is constructed at the SQL-level, not
the Haskell level. That’s quite nice.
~

Do you think it could be added to Opaleye ?

[delanoe]

Suppose I add (.@@.) operator to data BinOp in PrimQuery.hs (or Operators.hs?), I do not get how it is translated to the right sql query.

[tomjaguarpaw]

These instruction should see you through.

1. Add an operator in Operators.hs. I guess it would look like

    (@@) :: Column something -> Column something -> Column something
    (@@) = C.binOp (HPQ.:@@)
   

2. The type checker will guide you to add :@@ to BinOp in PrimQuery.hs.

3. The type checker will guide you to add a case for :@@ in showBinOp in Default.hs.

I think that's it! Try it and let me know how you get on.

[EDIT: Posting this as a new comment since I made too many mistakes in the last one]

[delanoe]

Hello, sorry for the late answer. I have added the operator, ok. The point is I am trying to mimic this kind of query:

SELECT field from table WHERE table.tsvector @@ (?::tsquery)
using (?) from postgresql-simple.

Do you have a suggestion to do it properly ?
Many thanks for your help

[delanoe]

Here are the types created for this:
https://gitlab.iscpif.fr/gargantext/haskell-gargantext/blob/master/src/Gargantext/Database/TextSearch.hs#L74
I would like to use opaleye Query power to use it.

[tomjaguarpaw]

I'm not sure what you mean. Opaleye doesn't have that type of parametrised query. Instead you just write a function!

[delanoe]

Ok, I have created the Types. Last step for joins, I need the NullMaker instance for joins:

No instance for (Default NullMaker (Column PGTSVector) (Column PGTSVector))

I do not see how to create this.

[delanoe]

delanoe@7ca760f

[tomjaguarpaw]

That instance doesn't make sense. NullMaker is supposed to make the output column nullable. The instances are in Internal.Join and are the only ones you should need.

[delanoe]

thanks, I have found it
I am almost done. It compiles now.
But I fail when parsing the tsvector type:
*** Exception: Incompatible {errSQLType = "tsvector",
errSQLTableOid = Just (Oid 359253), errSQLField =
"result8_3", errHaskellType = "Text", errMessage = "types
incompatible"}
(I put TSVector = Text thinking it would hack it first)

Do you have an idea how I could parse tsvector ?
(https://www.postgresql.org/docs/8.3/datatype-textsearch.html)
Maybe I am using a wrong way ?

[tomjaguarpaw]

Have you used TSVector with postgresql-simple before? I can't see support for it in FromField. If postgresql-simple doesn't support it then I think we need to open an issue on that package.

[delanoe]

No, I have not used TSVector with postgresql-simple before. I have created it since it did not exist already indeed. Yes, we can open an issue for that. I can do it and help if needed. Just I do not understand how to start with the parser.

[delanoe]

I have just created the issue: lpsmith/postgresql-simple#271

[tomjaguarpaw]

Closing as stale. Feel free to reopen if necessary.