5 ms·

Similar functionality already exists in some SQL implementations, notably DuckDB: create table macro author_books() as ( select a.name as au

Similar functionality already exists in some SQL implementations, notably DuckDB: create table macro author_books() as ( select a.name as author, b.title, from a inner join b on b.author_id = a.author_id ); is the same as the example in the article and it is called like: with a as (...), b as (...) from author_books(); https://duckdb.org/docs/sql/statements/create_macro.html#table-macros https://duckdb.org/docs/sql/statements/create_macro.html#tab...

_dark_matter_ 2y ago
I really dislike leading off with "create table". My brain is unable to read it except as DDL.
krembo 2y ago
Isn't this an abstraction of functions?
- tankenmate 2y ago
  It is an abstraction, it may be arguable if it is an abstraction of functions. I once built a web app where we had a library of CTEs that we used to manage all the layers of indirection in the data, it was performant and made it much easier for users to be able to search and manipulate the data; especially for ad hoc data analysis.
n_plus_1_acc 2y ago
How does it differ from a view?
- geysersam 2y ago
  It can take arguments. I didn't use any though so it's not a good example
typ 2y ago
DuckDB macro doesn't support instantiating a table macro using the `query_table` function. So, it's not possible to expand a table macro like `foo(bar(baz()))`, making its use limited.
- 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 ago
    Here 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 ago
      FROM ( (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.
jeltz 2y ago
How do these differ from SQL functions in PostgreSQL? They look like the same thing.
- geysersam 2y ago
  Yes but they are transparent to the optimizer so the argument against them in the blog article doesn't apply.
ncruces 2y ago
SQLite supports this with the help of a virtual table: https://github.com/0x09/sqlite-statement-vtab https://github.com/0x09/sqlite-statement-vtab Available in sqlean: https://github.com/nalgeon/sqlean/blob/main/docs/define.md#table-valued-functions https://github.com/nalgeon/sqlean/blob/main/docs/define.md#t... And my Go driver: https://pkg.go.dev/github.com/ncruces/go-sqlite3/ext/statement https://pkg.go.dev/github.com/ncruces/go-sqlite3/ext/stateme... Mine is a strict port, though admittedly, this could be improved with additional pushdown.

_dark_matter_ 2y ago

I really dislike leading off with "create table". My brain is unable to read it except as DDL.

krembo 2y ago

Isn't this an abstraction of functions?

tankenmate 2y ago
It is an abstraction, it may be arguable if it is an abstraction of functions. I once built a web app where we had a library of CTEs that we used to manage all the layers of indirection in the data, it was performant and made it much easier for users to be able to search and manipulate the data; especially for ad hoc data analysis.

n_plus_1_acc 2y ago

How does it differ from a view?

geysersam 2y ago
It can take arguments. I didn't use any though so it's not a good example

typ 2y ago

DuckDB macro doesn't support instantiating a table macro using the `query_table` function. So, it's not possible to expand a table macro like `foo(bar(baz()))`, making its use limited.

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 ago
  Here 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 ago
    FROM ( (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.

jeltz 2y ago

How do these differ from SQL functions in PostgreSQL? They look like the same thing.

geysersam 2y ago
Yes but they are transparent to the optimizer so the argument against them in the blog article doesn't apply.

ncruces 2y ago

SQLite supports this with the help of a virtual table: https://github.com/0x09/sqlite-statement-vtab https://github.com/0x09/sqlite-statement-vtab Available in sqlean: https://github.com/nalgeon/sqlean/blob/main/docs/define.md#table-valued-functions https://github.com/nalgeon/sqlean/blob/main/docs/define.md#t... And my Go driver: https://pkg.go.dev/github.com/ncruces/go-sqlite3/ext/statement https://pkg.go.dev/github.com/ncruces/go-sqlite3/ext/stateme... Mine is a strict port, though admittedly, this could be improved with additional pushdown.