Skip to main content

Module sql_macro

Module sql_macro 

Source
Expand description

SQL macro registration for DuckDB extensions.

SQL macros let you package reusable SQL expressions and queries as named DuckDB functions — no FFI callbacks required. This module provides a safe Rust builder for creating both scalar and table macros via CREATE OR REPLACE MACRO statements executed during extension initialization.

§Macro types

TypeSQLReturns
ScalarAS (expression)one value per row
TableAS TABLE querya table

§Example

use quack_rs::sql_macro::SqlMacro;
use quack_rs::error::ExtensionError;

fn register(con: libduckdb_sys::duckdb_connection) -> Result<(), ExtensionError> {
    unsafe {
        // Scalar macro: clamp(x, lo, hi) — no C++ needed!
        SqlMacro::scalar("clamp", &["x", "lo", "hi"], "greatest(lo, least(hi, x))")?
            .register(con)?;

        // Table macro: active_rows(tbl) — returns filtered rows. A table
        // parameter is read through `query_table`: a bare `FROM tbl` makes
        // DuckDB look for a table named `tbl` at registration, and fail.
        SqlMacro::table("active_rows", &["tbl"], "SELECT * FROM query_table(tbl) WHERE active = true")?
            .register(con)?;
    }
    Ok(())
}

§SQL injection safety

Macro names are validated against validate_function_name, and parameter names against validate_parameter_name: an ASCII letter or underscore followed by ASCII letters, digits or underscores ([A-Za-z_][A-Za-z0-9_]*, at most 256 characters), and not a DuckDB keyword that cannot be used in that position unquoted — measured, not guessed: coalesce or values cannot be called as a macro, and a parameter called order or left could only be referred to in the body as "order" / "left", so each is refused rather than left to fail at registration or inside the body. Mixed case is accepted.

The generated SQL always emits the names as double-quoted identifiers ("name"). The validated character set cannot contain ", so quoting needs no escaping. Quoting does not make the name case-sensitive: DuckDB resolves identifiers case-insensitively whether or not they were quoted, so a macro registered as MyMacro is callable as mymacro(...) or MYMACRO(...).

The SQL body (expression / query) is your own extension code, not user-supplied input. Never build macro bodies from untrusted runtime data. There is no escaping applied to the body. SqlMacro::register does refuse a body that turns the statement into several (1); DROP TABLE t; SELECT (1), using DuckDB’s own parser to count them, and a statement that does not parse at all, but a body can still change the meaning of the one statement it is part of. Nothing is executed in either refusal.

§Where a macro lives, and what that means

A macro is not a function registration: SqlMacro::register runs CREATE OR REPLACE MACRO on the connection, so the macro is an ordinary catalog object in the connection’s default database and schema — the user’s database. That has consequences a registered function does not:

  • It persists. In a database file the macro is written to disk and is still there in the next session, even if the extension is never loaded again. Loading the extension again is fine: CREATE OR REPLACE simply replaces it.
  • It needs a writable database. On a database opened read-only the CREATE fails, so an entry point that propagates the error with ? makes LOAD fail. Decide whether a macro is essential or can be skipped there.
  • It replaces a user’s macro of the same name, silently — that is what OR REPLACE means. Prefix macro names with the extension’s name.
  • It can shadow a built-in function. A macro named abs in the default schema is found before the built-in abs in the system catalog, so abs(-1) calls the macro.

Structs§

SqlMacro
A SQL macro definition ready to be registered with DuckDB.

Enums§

MacroBody
The body of a SQL macro: a scalar expression or a table query.