XTQL for selecting all columns in `information_schema` tables

Suppose we interact with an XTDB node via HTTP (related to a previous topic ). The rows of the information_schema table (e.g. information_schema.tables) can be returned by calling:

;; Require `xtdb.client` as `xtc`; `xtdb.api` as `xt`
;; XTDB node accessible at `http-node-url`
(with-open [node (xtc/start-client http-node-url)]
    (xt/q node "SELECT * FROM information_schema.tables") ; (1)

Resulting in something like:

[{:table-name "foo", :table-type "BASE TABLE", :table-catalog "xtdb", :table-schema "public"}
 {:table-name "xt$txs", :table-type "BASE TABLE", :table-catalog "xtdb", :table-schema "public"}]

Replacing the code at (1) with the following:

(xt/q node '(from :information-schema.tables [*])

Returns something like:

[{} {}]

(One empty map per row.)

Table columns can be specified to be returned, e.g.:

(xt/q node '(from :information-schema.tables [table-name])

Which would return something like:

[{:table-name "foo"} {:table-name "xt$txs"}]

Is there a way to return all columns of :information-schema.tables without specifying them?

This might be related to this earlier topic.

Ah, so XTQL is ‘behind’ SQL for various functionality currently, and in this case INFORMATION_SCHEMA has only been implemented with SQL in mind (aiming for Metabase etc. integration). The fix to add support in XTQL should be small, but for now though you will have to reach for SQL for some things like this. Apologies for the confusion!

1 Like

Thanks @refset!

Admittedly, very much interested to see how XTQL grows. :innocent:

1 Like