Support for postgres "with ordinality"

[danielhvs]

Since 9.4 postgres has the with ordinality construct:
https://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number/8767450#8767450

We want to build a query like:
SELECT "item", "index" FROM "t", jsonb_array_elements("j") with ordinality arr("item", "index")

Is there a way to do it with the regular honeysql constructs? How?
If not, could honeysql add support for it?

Tests:
CREATE TABLE t ( "j" JSONB DEFAULT '[]' NOT NULL);
insert into t(j) values ( '[{"a": 1}, {"a": 2}]' );
SELECT "item", "index" FROM "t", jsonb_array_elements("j") with ordinality arr("item", "index") WHERE 1 = 1

We ended up with the following workaround in order to avoid using :raw due to security reasons:

(ns test
  (:require
   [honey.sql :as sql]
   [honey.sql.helpers :as hh]))

(sql/register-fn! :jsonb_array_elements-with-ordinality
                  (fn [_ [jsbon-arr item index]]
                    (let [[sql-jsbon-arr & params-sql-jsbon-arr] (sql/format-expr jsbon-arr)
                          [sql-item & params-item] (sql/format-expr item)
                          [sql-index & params-index] (sql/format-expr index)]
                      (-> [(str "jsonb_array_elements(" sql-jsbon-arr ")" " with ordinality arr(" sql-item ", " sql-index ")")]
                          (into params-sql-jsbon-arr)
                          (into params-item)
                          (into params-index)))))

(sql/format (-> (hh/select :item :index)
                (hh/from :t [[:jsonb_array_elements-with-ordinality :j :item :index]])))

Maybe this is out of scope of honeysql due to be too specific to postgres. Maybe not.
Thanks for honeysql!

[seancorfield]

It's an ANSI SQL construct apparently so I'll add support. I suspect it may be as simple as:

(sql/register-op! :with-ordinality)

and then you could write something like:

[:with-ordinality [:jsonb_array_elements [:inline "j"]] [:arr [:inline "item" "index"]]]

Like so:

user=> (require '[honey.sql :as sql])
nil
user=> (sql/register-op! :with-ordinality)
nil
user=> (sql/format [:with-ordinality [:jsonb_array_elements [:inline "j"]] [:arr [:inline "item" "index"]]])
["JSONB_ARRAY_ELEMENTS('j') WITH ORDINALITY ARR('item')"]
user=>