Error when using RETURNING from PostgreSQL about hugsql HOT 12 CLOSED

Thanks @rcanepa I'm also doing something similar

from hugsql.

@csummers Is this feature supposed to work for UPDATE statements too? Postgres also supports this.

from hugsql.

This happens because statements executed with execute (:! in HugSQL) are not expected to return result sets. You can get around this issue by using :? for your insert w/ returning statements.

However, I'm going to keep this open. The HugSQL docs should indicate this, I should add a test to show this, and I'm thinking about adding a command type that would indicate this. Also, this is tangentially related to issue #8.

from hugsql.

Hi @sthomp, I use that pattern (:i*:fields) in combination with something like this:

(s/defn create-office :- {s/Keyword s/Uuid}
  [db :- ConnectionMap
   office :- schemas/Office]
  (let [office (select-keys office schemas/office-db-fields) ;; remove read only fields
        fields (map name (keys office)) ;; get fields as strings
        values (vals office) ;; get values
        sql-args {:fields fields
                  :values values}]
    (execute! db :insert-office sql-args)))

from hugsql.

I can confirm this bug. I noticed it yesterday. My exception was "Received result when none was expected"

Using the latest version of hugsql, and funcool/clojure.jdbc adapter.

from hugsql.

@csummers great!. I can confirm that it solves the problem. All I had to do was change my query to something like this:

-- :name insert-contact! :? :1
-- :doc Insert a contact record.
INSERT INTO contacts
(:i*:fields)
VALUES
(:v*:values)
RETURNING id;

Thanks!

from hugsql.

As of the 0.4.0, there is a :command type of :returning-execute, or :<! for short. This still executes as a query like :?, but provides a bit more semantic value. See Insert for an example.

from hugsql.

Excellent!... I will update my code and let you know if I something doesn't work!... Keep the good work!!

from hugsql.

Just came across this issue and wanted to suggest adding an example to the Insert examples of how to insert with dynamic columns (ie: the (:i*:fields) notation used above). All the current examples use fixed columns.

from hugsql.

Thanks for the suggestion. I could add a couple of examples to this effect. I think a likely scenario instead of the separate fields and values vectors would use a hashmap of {"field1" "value1"...}. This is hinted at with the generic update example in the Clojure Expressions portion of the docs.

from hugsql.

That would be really nice. The {field value .. syntax is probably the best feature about Korma that other libraries are missing

from hugsql.

Works with upsert using INSERT ON CONFLICT statement as well, if there was any doubt.

from hugsql.