sqldynamic-sqlduckdb

DuckDB: how do I create a table containing a column with a specific name from a `CREATE MACRO ... AS TABLE ...`?


Suppose I have the following macro:

CREATE OR REPLACE MACRO hello_world(col_name, series_start, series_end) AS TABLE (
    SELECT generate_series::VARCHAR AS col_name
    FROM generate_series(series_start, series_end)
  );
CREATE OR REPLACE TABLE tbl AS
FROM hello_world('world', 3, 5);
SHOW TABLES;

Execution produces:

┌─────────┐
│  name   │
│ varchar │
├─────────┤
│ tbl     │
└─────────┘

┌──────────┐
│ col_name │
│ varchar  │
├──────────┤
│ 3        │
│ 4        │
│ 5        │
└──────────┘

But I wanted a column named 'world', yet I got a column named col_name (the macro parameter's name): so how do I correctly make sure that the macro parameter's value is used as a column name?


Solution

  • In connection with a similar question, one of the DuckDB powers-that-be wrote:

    I think there is some confusion here in how macro parameters work. Macro parameters are expressions, as such they can only replace expressions in a query.

    The author goes on to emphasize the distinction between identifiers and expressions.

    As a rule of thumb, this can be taken to mean that if a formal argument is being ignored, that's because of the principle enunciated above. In other words, it's a feature. Which is not to say an enhancement request couldn't be made. In the meantime, you could of course alter table tbl rename 'col_name' to 'world';