Question on how to use jsonb_set with postgres about hugsql HOT 4 CLOSED

@create-renegr Hi! In order to take advantage of the sql-injection protecting parameter-binding features of HugSQL's value parameters, you will need to take a look at extending the underlying clojure.java.jdbc protocols to support a Clojure data type (either a built-in type or a type/record of your own) to be treated as a jsonb object.

Have a look at the following blog post for examples of extending these protocols to support postgresql's json data type. The jsonb would likely be similar.

http://hiim.tv/clojure/2014/05/15/clojure-postgres-json/

from hugsql.

Hi,

thanks for your quick response.

The postgres error message I receive when using just :val instead of :sql:val is this:

ERROR: function jsonb_set(jsonb, unknown, character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 65

Does this mean, that I need to make sure, that instead of "charactery varying" I get something like String?

I don't understand why I need to make a protocol to support Clojure Strings. Because the :val is just a plain String, which is already supported by HugSQL.

I can't see (yet), why your proposed solution would solve my actual problem.

Kind regards,

René

from hugsql.

@create-renegr Hey, sorry for the slow reply on this. I missed your response the other day.

You are right: for jsonb_set usage you do not need to implement the protocols I suggested as long as you do the following:

1. Provide explicit type casts to Postgresql for your json fields (this is hinted at in the error you are receiving).
2. Pass in valid json (for json string, it needs the double quote) when calling your Clojure function.

Here's an example:

(deftest q52
  (testing "pg jsonb_set"
    (let [db (:postgresql dbs)]
      (hugsql/db-run db "drop table if exists t1" {} :!)
      (hugsql/db-run db "create table t1 (id serial primary key, props jsonb)" {} :!)
      (hugsql/db-run db "insert into t1 (props) values ('{\"attr\": \"value-1\"}')" {} :!)
      (hugsql/db-run db
                     "update t1 set props = jsonb_set(props, '{\"attr\"}', :val::jsonb) where id = :id"
                     {:id 1 :val "\"value-2\""} :!)
      (hugsql/db-run db "drop table t1" {} :!))))

from hugsql.

That did the job!!

Thank you very much for the explanation. Now that makes sense. Wasn't sure which layer had to provide the explicit typecast.

Closing the question, since you provided a clear answer.

from hugsql.