5 ms·
Perhaps I don't fully understand what you're saying CREATE TABLE person (name VARCHAR, age BIGINT); INSERT INTO person VALUES ('Alice', 37), ('Ana', 35
by chrisjc 2y ago
Perhaps I don't fully understand what you're saying
CREATE TABLE person (name VARCHAR, age BIGINT);
INSERT INTO person VALUES ('Alice', 37), ('Ana', 35), ('Bob', 41), ('Bea', 25);
CREATE OR REPLACE MACRO my_query_table(t) AS TABLE (SELECT * FROM query_table(t));
SELECT * FROM my_query_table('person');
Or do you mean that you can't use `query_table` with a macro identifier in place of the table identifier as input (after all, where would put the macro args)? Perhaps using `query(...)` instead would work?
CREATE OR REPLACE MACRO query_macro(m, i) AS TABLE (SELECT * FROM query(CONCAT('SELECT * FROM ', m::STRING, $$($$, i::STRING, $$)$$)));
SELECT * FROM query_macro('my_query_table', 'person');
Or maybe through some other kind of gymnastics
https://duckdb.org/2024/03/01/sql-gymnastics.html https://duckdb.org/2024/03/01/sql-gymnastics.html
- typ 2y agoHere is a contrived example: CREATE MACRO foo(bar, baz) AS TABLE WITH base AS ( FROM query_table(bar) t ), refined AS ( FROM base JOIN query_table(baz) u ON base.id = u.id ) SELECT * FROM refined; I want to test many user-defined combinations of (bar, baz). How can I pass a macro instance to query_table like FROM ( (FROM foo(mybar1(some_arg1, some_arg2), mybaz101(500))) UNION ALL (FROM foo(mybar2(some_arg3, some_arg4), mybaz201(300))) UNION ALL ... ) SELECT *; Your second example using the 'query_macro' looks like something I was looking for. But it doesn't seem to be of general use that supports an arbitrary number of arguments.
- geysersam 2y agoFROM ( (with baz as (from baz101(500)), bar as (from bar1(arg1, arg2)) from foo ) UNION ALL (with baz as (from baz201(300)), bar as (from bar2(arg3, arg4)) from foo ) UNION ALL ... ) SELECT *; When you pass table arguments to a macro, don't pass them as a parameter to the macro, instead make a subquery and give it the name that `foo` expects.
- typ 2y agoNot as terse as I imagined it could be, but I really didn't expect that it could be done this way. Thanks!