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

Question

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?

jqurious · Accepted Answer · 2024-09-09 16:29:30Z

2

You can build a query string and execute it with query()

duckdb.sql("""
create or replace macro hello_world(col_name, series_start, series_end) as table (
   from query(format(
      '
      select generate_series::varchar as {}
      from generate_series({}, {})
      ', 
      col_name, series_start, series_end
   ))
)
""")

duckdb.sql("""
create or replace table tbl as from hello_world('world', 3, 5);
from tbl
""")

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

edited Sep 9, 2024 at 16:29

answered Sep 4, 2024 at 9:44

jqurious

24.2k6 gold badges24 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

peak · Accepted Answer · 2024-09-03 01:32:17Z

1

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';

edited Sep 3, 2024 at 1:32

answered Sep 3, 2024 at 1:25

peak

119k21 gold badges185 silver badges218 bronze badges

2 Comments

bzm3r Over a year ago

Thank you. I think it's very weird that I cannot use a variable as a variable. I could do alter table tbl rename 'col_name' to 'world'; but that ignores the fact that I do not know that col_name is 'world' for the use case I was thinking of --- col_name is truly a variable. Overall, the answer that one seems to be lead by this issue together with others is that DuckDB SQL is that it's meant to be generated by some other language (e.g. using Python).

peak Over a year ago

Yes, "macro" is very misleading. An alternative to a programming language approach would be "dynamic sql" based on the DuckDB CLI (e.g. using .once and .read). But an enhancement request might make a difference.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related