Parser Tools

An experimental DuckDB extension that exposes functionality from DuckDB's native SQL parser.

Overview

parser_tools is a DuckDB extension designed to provide SQL parsing capabilities within the database. It allows you to analyze SQL queries and extract structural information directly in SQL. This extension provides parsing functions for tables, WHERE clauses, function calls, and statements.

Features

• Extract table references from a SQL query with context information (e.g. FROM, JOIN, etc.)
• Extract function calls from a SQL query with context information (e.g. SELECT, WHERE, HAVING, etc.)
• Parse WHERE clauses to extract conditions and operators
• Parse multi-statement SQL to extract individual statements or count the number of statements
• Support for window functions, nested functions, and CTEs
• Includes schema, name, and context information for all extractions
• Built on DuckDB's native SQL parser
• Simple SQL interface — no external tooling required

Known Limitations

• Only SELECT statements are supported for table and function parsing
• WHERE clause parsing supports additional statement types
• Full parse tree is not exposed (only specific structural elements)

Installation

INSTALL parser_tools FROM community;;
LOAD parser_tools;

Usage

Parse table references from a query

Simple example

SELECT * FROM parse_tables('SELECT * FROM MyTable');

Output

┌─────────┬─────────┬─────────┐
│ schema  │  table  │ context │
│ varchar │ varchar │ varchar │
├─────────┼─────────┼─────────┤
│ main    │ MyTable │ from    │
└─────────┴─────────┴─────────┘

This tells you that MyTable in the main schema was used in the FROM clause of the query.

CTE Example

select * from parse_tables('with EarlyAdopters as (select * from Users where id < 10) select * from EarlyAdopters;');

Output

┌─────────┬───────────────┬──────────┐
│ schema  │     table     │ context  │
│ varchar │    varchar    │ varchar  │
├─────────┼───────────────┼──────────┤
│         │ EarlyAdopters │ cte      │
│ main    │ Users         │ from     │
│ main    │ EarlyAdopters │ from_cte │
└─────────┴───────────────┴──────────┘

This tells us a few things:

• EarlyAdopters was defined as a CTE.
• The Users table was referenced in a from clause.
• EarlyAdopters was referenced in a from clause (but it's a cte, not a table).

Context

Context helps identify where elements are used in the query.

Table Context

• from: table in the main FROM clause
• join_left: left side of a JOIN
• join_right: right side of a JOIN
• cte: a Common Table Expression being defined
• from_cte: usage of a CTE as if it were a table
• subquery: table reference inside a subquery

Function Context

• select: function in a SELECT clause
• where: function in a WHERE clause
• having: function in a HAVING clause
• order_by: function in an ORDER BY clause
• group_by: function in a GROUP BY clause
• nested: function call nested within another function

Functions

This extension provides parsing functions for tables, functions, WHERE clauses, and statements. Each category includes both table functions (for detailed results) and scalar functions (for programmatic use).

In general, errors (e.g. Parse Exception) will not be exposed to the user, but instead will result in an empty result. This simplifies batch processing. When validity is needed, is_parsable can be used.

Function Parsing Functions

These functions extract function calls from SQL queries, including window functions and nested function calls.

parse_functions(sql_query) – Table Function

Parses a SQL SELECT query and returns all function calls along with their context of use (e.g. select, where, having, order_by, etc.).

Usage

SELECT * FROM parse_functions('SELECT upper(name), count(*) FROM users WHERE length(email) > 0;');

Returns

A table with:

• function_name: the name of the function
• schema: schema name (default "main" if unspecified)
• context: where the function appears in the query

Example

SELECT * FROM parse_functions($$
    SELECT upper(name), count(*) 
    FROM users 
    WHERE length(email) > 0 
    GROUP BY substr(department, 1, 3)
    HAVING sum(salary) > 100000
    ORDER BY lower(name)
$$);

function_name	schema	context
upper	main	select
count_star	main	select
length	main	where
substr	main	group_by
sum	main	having
lower	main	order_by

---

parse_function_names(sql_query) – Scalar Function

Returns a list of function names (strings) referenced in the SQL query.

Usage

SELECT parse_function_names('SELECT upper(name), lower(email) FROM users;');
----
['upper', 'lower']

Returns

A list of strings, each being a function name.

Example

SELECT parse_function_names('SELECT rank() OVER (ORDER BY salary) FROM users;');
----
['rank']

---

parse_functions(sql_query) – Scalar Function (Structured)

Similar to the table function, but returns a list of structs instead of a result table. Each struct contains:

• function_name (VARCHAR)
• schema (VARCHAR)
• context (VARCHAR)

Usage

SELECT parse_functions('SELECT upper(name), count(*) FROM users;');
----
[{'function_name': upper, 'schema': main, 'context': select}, {'function_name': count_star, 'schema': main, 'context': select}]

Returns

A list of STRUCTs with function name, schema, and context.

Example with filtering

SELECT list_filter(parse_functions('SELECT upper(name) FROM users WHERE lower(email) LIKE "%@example.com"'), f -> f.context = 'where') AS where_functions;
----
[{'function_name': lower, 'schema': main, 'context': where}]

---

Table Parsing Functions

parse_tables(sql_query) – Table Function

Parses a SQL SELECT query and returns all referenced tables along with their context of use (e.g. from, join_left, cte, etc.).

Usage

SELECT * FROM parse_tables('SELECT * FROM my_table JOIN other_table USING (id)');

Returns

A table with:

• schema: schema name (default "main" if unspecified)
• table: table name
• context: where the table appears in the query
  One of: from, join_left, join_right, from_cte, cte, subquery

Example

SELECT * FROM parse_tables($$
    WITH cte1 AS (SELECT * FROM x)
    SELECT * FROM cte1 JOIN y ON cte1.id = y.id
$$);

schema	table	context
	cte1	cte
main	x	from
main	y	join_right
	cte1	from_cte

---

parse_table_names(sql_query [, exclude_cte=true]) – Scalar Function

Returns a list of table names (strings) referenced in the SQL query. Can optionally exclude CTE-related references.

Usage

SELECT parse_table_names('SELECT * FROM my_table');
----
['my_table']

Optional Parameter

SELECT parse_table_names('with cte_test as(select 1) select * from MyTable, cte_test', false); -- include CTEs
---- 
[cte_test, MyTable, cte_test]

Returns

A list of strings, each being a table name.

Example

SELECT parse_table_names('SELECT * FROM a JOIN b USING (id)');
----
['a', 'b']

---

parse_tables(sql_query) – Scalar Function (Structured)

Similar to the table function, but returns a list of structs instead of a result table. Each struct contains:

• schema (VARCHAR)
• table (VARCHAR)
• context (VARCHAR)

Usage

SELECT parse_tables('select * from MyTable');
----
[{'schema': main, 'table': MyTable, 'context': from}]

Returns

A list of STRUCTs with schema, table name, and context.

Example

SELECT parse_tables('select * from MyTable t inner join Other o on o.id = t.id');
----
[{'schema': main, 'table': MyTable, 'context': from}, {'schema': main, 'table': Other, 'context': join_right}]

is_parsable(sql_query) – Scalar Function

Checks whether a given SQL string is syntactically valid (i.e. can be parsed by DuckDB).

Usage

SELECT is_parsable('SELECT * FROM users');
-- true

SELECT is_parsable('SELEKT * FROM users');
-- false

Returns

A boolean indicating whether the input SQL string is parsable (true) or not (false).

Example

SELECT query, is_parsable(query) AS valid
FROM (VALUES
    ('SELECT * FROM good_table'),
    ('BAD SQL SELECT *'),
    ('WITH cte AS (SELECT 1) SELECT * FROM cte')
) AS t(query);

Output

┌───────────────────────────────────────────────┬────────┐
│                    query                      │ valid  │
│                   varchar                     │ boolean│
├───────────────────────────────────────────────┼────────┤
│ SELECT * FROM good_table                      │ true   │
│ BAD SQL SELECT *                              │ false  │
│ WITH cte AS (SELECT 1) SELECT * FROM cte      │ true   │
└───────────────────────────────────────────────┴────────┘

---

Statement Parsing Functions

These functions parse multi-statement SQL strings and extract individual statements or count them.

parse_statements(sql_query) – Table Function

Parses a SQL string containing multiple statements and returns each statement as a separate row.

Usage

SELECT * FROM parse_statements('SELECT 42; SELECT 43;');

Returns

A table with:

• statement: the SQL statement text

Example

SELECT * FROM parse_statements($$
    SELECT * FROM users WHERE active = true;
    INSERT INTO log VALUES ('query executed');
    SELECT count(*) FROM transactions;
$$);

statement
SELECT * FROM users WHERE (active = true)
INSERT INTO log (VALUES ('query executed'))
SELECT count_star() FROM transactions

---

parse_statements(sql_query) – Scalar Function

Returns a list of statement strings from a multi-statement SQL query.

Usage

SELECT parse_statements('SELECT 42; SELECT 43;');
----
[SELECT 42, SELECT 43]

Returns

A list of strings, each being a SQL statement.

Example

SELECT parse_statements('SELECT 1; INSERT INTO test VALUES (2); SELECT 3;');
----
[SELECT 1, 'INSERT INTO test (VALUES (2))', SELECT 3]

---

num_statements(sql_query) – Scalar Function

Returns the number of statements in a multi-statement SQL query.

Usage

SELECT num_statements('SELECT 42; SELECT 43;');
----
2

Returns

An integer count of the number of SQL statements.

Example

SELECT num_statements($$
    WITH cte AS (SELECT 1) SELECT * FROM cte;
    UPDATE users SET last_seen = now();
    SELECT count(*) FROM users;
    DELETE FROM temp_data;
$$);
----
4

---

Development

Build steps

To build the extension, run:

GEN=ninja make

The main binaries that will be built are:

./build/release/duckdb
./build/release/test/unittest
./build/release/extension/parser_tools/parser_tools.duckdb_extension

• duckdb is the binary for the duckdb shell with the extension code automatically loaded.
• unittest is the test runner of duckdb. Again, the extension is already linked into the binary.
• parser_tools.duckdb_extension is the loadable binary as it would be distributed.

Running the extension

To run the extension code, simply start the shell with ./build/release/duckdb (which has the parser_tools extension built-in).

Now we can use the features from the extension directly in DuckDB:

D select * from parse_tables('select * from MyTable');
┌─────────┬─────────┬─────────┐
│ schema  │  table  │ context │
│ varchar │ varchar │ varchar │
├─────────┼─────────┼─────────┤
│ main    │ MyTable │ from    │
└─────────┴─────────┴─────────┘

Running the extension from a duckdb distribution

To run the extension dev build from an existing distribution of duckdb (e.g. cli):

$ duckdb -unsigned

D install parser_tools from './build/release/repository/v1.2.1/osx_amd64/parser_tools.duckdb_extension';
D load parser_tools;

D select * from parse_tables('select * from MyTable');
┌─────────┬─────────┬─────────┐
│ schema  │  table  │ context │
│ varchar │ varchar │ varchar │
├─────────┼─────────┼─────────┤
│ main    │ MyTable │ from    │
└─────────┴─────────┴─────────┘

Running the tests

See Writing Tests to learn more about duckdb's testing philosophy. To that end, we define tests in sql at: test/sql.

The tests can be run with:

make test

and easily re-ran as changes are made with:

GEN=ninja make && make test