Add Support for Dynamic SQL Macros for Flexible Column Selection

[berkaysynnada]

Is your feature request related to a problem or challenge?

People may need to write flexible SQL queries that adapt to different table schemas without hardcoding column names.

Query reusability is very limited currently, and creating dynamic queries requires external templating. A built-in mechanism to define and use dynamic SQL macros would significantly enhance the expressiveness and reusability

Describe the solution you'd like

Introduce support for dynamic SQL macros that allow users to parameterize column selection. For example, a macro could accept lists of columns to include or exclude and then automatically select the appropriate columns from a given table or CTE. An example usage might look like this:

CREATE OR REPLACE MACRO dynamic_select(
    include_cols, 
    exclude_cols 
) AS TABLE (
    FROM source_table
    SELECT
        COLUMNS(col -> 
            (list_contains(include_cols, col))
            AND
            (NOT list_contains(exclude_cols, col))
        )
);

WITH source_table AS (
    SELECT * FROM employees
)
FROM dynamic_select(
    ['id', 'name', 'department'], 
    ['salary']                 
);

Describe alternatives you've considered

No response

Additional context

No response

[khushishukla2813]

Hey @berkaysynnada Im interested in this issue and wanna participate in GSoC. Before making changes I want to understand where to store macro definitions in memory, catalog, or elsewhere nd should I modify parser.rs to support CREATE MACRO nd also how macros gonna integrate into query execution,,,

[berkaysynnada]

where to store macro definitions in memory, catalog, or elsewhere
how macros gonna integrate into query execution

These questions are not easy to answer straightforwardly at the moment. Once the project assignments are done, we will focus on these questions during the first week and likely discuss them with the entire community. However, if you develop some intuition and can make proposals, it will certainly be beneficial.

should I modify parser.rs to support CREATE MACRO

Yeah, you probably should