Question: Combining the concepts of Postgresql data types and Value List Parameters.

[mchughs]

For a toy example consider I have an SQL table fish. One of the columns of fish is scaleColor where the value can be a member of an enumeration defined as

CREATE TYPE color AS ENUM ('blue', 'red', 'black', 'white');

I would like to fetch all the fish where the color is 'blue' or 'red'. My naive hugsql query would look like,

-- :name get-fish-by-color :? :*
-- :doc returns all fish which have a scale color in the color list provided.
SELECT * FROM fish
WHERE scaleColor IN (:v*:color-list::color);

(get-fish-by-color conn {:color-list ["blue", "red"]})

Executing this clojure code will throw the error ERROR: operator does not exist: color = character varying Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

My questions are;

1. Are value-lists and Postgresql data types conjoinable concepts in hugsql.
2. If yes, then what is the proper syntax to achieve my desired result.

I tried also making use of SQL CAST but couldn't find a way to make it work with my list.

[csummers]

In order to make this work, you have to cast each value separately.

The following example is from this SO post: https://stackoverflow.com/questions/51546003/casting-multiple-values-in-hugsql-or-yesql-with-postgres/51600434#51600434

You can generate a cast for each value in your vector in HugSQL by using a Clojure Expression:

-- :name x 
-- :require [clojure.string :as string]
select * from test where id in (
/*~
(clojure.string/join 
  ","
  (map-indexed (fn [i v] (str ":values." i "::int")) (:values params)))
~*/
)

Which will end up giving you something like this:

(x-sqlvec {:values ["1" "2"]})
;=> ["select * from test where id in (?::int,?::int)" "1" "2"]

So, the above takes the values vector and uses HugSQL's deep-get syntax to pull in each of the values individually and add the type cast to each one. So, you're effectively building a new set of HugSQL parameters on the fly that looks like:

`in (:values.0::int, :values.1::int, :values.2::int)`

Hope that helps!

[mchughs]

Definitely helps and works!

Though this does feel a bit more hands-on than I would expect. I'll see if I can make a PR that treats my naive syntax of :v*:color-list::color as syntactic sugar for the clojure expression you cited. What do you think?

[csummers]

I replied to the PR. My instinct here is to continue to not parse/decipher/validate any actual SQL and leave that to the database.

I realize that this may be a common occurrence, but there are at least three work-arounds for this--none of which seem too painful or hands-on.

• use PG's array syntax instead of IN (the first answer in the SO post)
• use the Clojure Expression inline
• create a reusable function to do the same thing as the inline expression, use -- :require, and a Clojure expression to call it in your SQL

[mchughs]

Works for me!