DuckDB: how do I use the result of a query on `DESCRIBE` to `SELECT` from a table?

Question

Suppose I want to select all columns of a certain type from a DuckDB table. For example, selecting all VARCHAR type columns, after creating a table like:

CREATE TABLE dummy (x VARCHAR, y BIGINT, z VARCHAR);
INSERT INTO dummy
VALUES ('a', 0, 'a'),
  ('b', 1, 'b'),
  ('c', 2, 'c');

Inngeneral, I might have an arbitrary number of VARCHAR type columns, so this query should be "dynamic". I get a list of the relevant columns using DESCRIBE:

SELECT column_name
FROM (DESCRIBE dummy)
WHERE column_type = 'VARCHAR';

This statement gives me a list of the column names which have type VARCHAR. But how do I use this? I tried using the COLUMNS expression:

SELECT COLUMNS(
    c->c IN (
      SELECT column_name
      FROM (DESCRIBE dummy)
      WHERE column_type = 'VARCHAR'
    )
  )
FROM dummy

But this gives me the error: BinderException: Binder Error: Table function cannot contain subqueries. I don't really understand the error. I get the same error when trying:

SELECT COLUMNS(
    c->list_contains(
      (
        SELECT column_name
        FROM (DESCRIBE dummy)
        WHERE column_type = 'VARCHAR'
      ),
      c
    )
  )
FROM dummy

How do I connect the dots between getting a list of columns by querying DESCRIBE tbl, and then using that list to select from tbl?

Answer 1

SQL-level variables were added in DuckDB 1.1.0

e.g. SET VARIABLE / GETVARIABLE

duckdb.sql("""
SET VARIABLE VARCHAR_NAMES = (
   SELECT LIST(column_name)
   FROM (DESCRIBE dummy)
   WHERE column_type = 'VARCHAR'
)
""")

duckdb.sql("""
FROM DUMMY SELECT COLUMNS(x -> x in GETVARIABLE('VARCHAR_NAMES'))
""")

┌─────────┬─────────┐
│    x    │    z    │
│ varchar │ varchar │
├─────────┼─────────┤
│ a       │ a       │
│ b       │ b       │
│ c       │ c       │
└─────────┴─────────┘