pg array constructor (:'ARRAY) gets quoted when :quoted is true

[liwp]

I'm trying to use the pg "array constructor" as described here, eg:

user=> (sql/format {:select [[[:'ARRAY {:select :oid :from :pg_proc :where [:like :proname [:inline "bytea%"]]}]]]})
["SELECT ARRAY (SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%')"]

But when I pass :quoted true to sql/format the ARRAY gets quoted as well:

user=> (sql/format {:select [[[:'ARRAY {:select :oid :from :pg_proc :where [:like :proname [:inline "bytea%"]]}]]]} :quoted true)
["SELECT \"ARRAY\" (SELECT \"oid\" FROM \"pg_proc\" WHERE \"proname\" LIKE 'bytea%')"]

And pg doesn't like it (second example):

postgres=# SELECT ARRAY (SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
                                        array
--------------------------------------------------------------------------------------
 {3331,3544,3543,2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412,2413}
(1 row)

postgres=# SELECT "ARRAY" (SELECT "oid" FROM "pg_proc" WHERE "proname" LIKE 'bytea%');
ERROR:  syntax error at or near "SELECT"
LINE 1: SELECT "ARRAY" (SELECT "oid" FROM "pg_proc" WHERE "proname" ...

My workaround is to format the subquery first with :quoted true, then write the ARRAY function call as raw SQL, and finally format the complete query again with :quoted true:

(let [sub-query (-> {:select :oid :from :pg_proc :where [:like :proname [:inline "bytea%"]]}
                    (sql/format :quoted true)
                    first)]
  (sql/format {:select [[[:raw (str "ARRAY(" sub-query ")")]]]} :quoted true))
["SELECT ARRAY(SELECT \"oid\" FROM \"pg_proc\" WHERE \"proname\" LIKE 'bytea%')"]

I'm happy to be told I'm doing it wrong 🙂

[seancorfield]

Yeah, I recently extended the :' "escape" syntax to another context and noticed this difference so I'm giving it some hammock time to try to decide what's the right thing to do here...

[seancorfield]

I consider this a bug but have not yet decided how to fix it.

[liwp]

Thanks for the update! As I wrote, we have a workaround so it's not urgent for us.