INSTALL parser_tools FROM community;
LOAD parser_tools;

-- Extract table references from a simple query
SELECT * FROM parse_tables('SELECT * FROM my_table');
┌─────────┬───────────┬─────────┐
│ schema  │  table    │ context │
│ varchar │  varchar  │ varchar │
├─────────┼───────────┼─────────┤
│ main    │ my_table  │ from    │
└─────────┴───────────┴─────────┘

-- Parse a query with a CTE and a join
SELECT * FROM parse_tables($$
    WITH recent_users AS (SELECT * FROM users WHERE created_at > now() - INTERVAL '7 days')
    SELECT * FROM recent_users r JOIN logins l ON r.id = l.user_id
$$);
┌─────────┬──────────────┬────────────┐
│ schema  │    table     │ context    │
│ varchar │   varchar    │ varchar    │
├─────────┼──────────────┼────────────┤
│         │ recent_users │ cte        │
│ main    │ users        │ from       │
│ main    │ logins       │ join_right │
│ main    │ recent_users │ from_cte   │
└─────────┴──────────────┴────────────┘

-- Return a list of table names from a query
SELECT parse_table_names('SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id');
┌────────────────────────────────────┐
│           parse_table_names        │
│             varchar[]              │
├────────────────────────────────────┤
│ ['orders', 'customers']            │
└────────────────────────────────────┘

-- Parse queries from a csv file
SELECT parse_table_names(query) AS tables FROM 'user_queries.csv';
┌───────────────────────────────┐
│            tables             │
│          varchar[]            │
├───────────────────────────────┤
│ ['users']                     │
│ ['orders', 'customers']       │
└───────────────────────────────┘

-- Structured output as a list of table references
SELECT parse_tables('SELECT * FROM products p JOIN inventory i ON p.sku = i.sku');
┌────────────────────────────────────────────────────────────────────────────┐
│                              parse_tables                                  │
│ list<struct<schema: varchar, table: varchar, context: varchar>>            │
├────────────────────────────────────────────────────────────────────────────┤
│ [{'schema': 'main', 'table': 'products',  'context': 'from'},              │
│  {'schema': 'main', 'table': 'inventory', 'context': 'join_right'}]        │
└────────────────────────────────────────────────────────────────────────────┘

-- Detect invalid sql
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);
┌───────────────────────────────────────────────┬────────┐
│                    query                      │ valid  │
│                   varchar                     │ boolean│
├───────────────────────────────────────────────┼────────┤
│ SELECT * FROM good_table                      │ true   │
│ BAD SQL SELECT *                              │ false  │
│ WITH cte AS (SELECT 1) SELECT * FROM cte      │ true   │
└───────────────────────────────────────────────┴────────┘

-- Extract WHERE conditions from a query
SELECT * FROM parse_where('SELECT * FROM MyTable WHERE time > 1 AND time < 100');
┌────────────────┬────────────┬─────────┐
│   condition    │ table_name │ context │
│    varchar     │  varchar   │ varchar │
├────────────────┼────────────┼─────────┤
│ ("time" > 1)   │ MyTable    │ WHERE   │
│ ("time" < 100) │ MyTable    │ WHERE   │
└────────────────┴────────────┴─────────┘

-- Return detailed condition breakdown from a query
SELECT * FROM parse_where_detailed('SELECT * FROM MyTable WHERE time > 1 AND time < 100');
┌─────────────┬───────────────┬─────────┬────────────┬─────────┐
│ column_name │ operator_type │  value  │ table_name │ context │
│   varchar   │    varchar    │ varchar │  varchar   │ varchar │
├─────────────┼───────────────┼─────────┼────────────┼─────────┤
│ time        │ >             │ 1       │ MyTable    │ WHERE   │
│ time        │ <             │ 100     │ MyTable    │ WHERE   │
└─────────────┴───────────────┴─────────┴────────────┴─────────┘

-- Parse a query with a BETWEEN condition
SELECT * FROM parse_where('SELECT * FROM MyTable WHERE time BETWEEN 1 AND 100');
┌────────────────────────────┬────────────┬─────────┐
│         condition          │ table_name │ context │
│          varchar           │  varchar   │ varchar │
├────────────────────────────┼────────────┼─────────┤
│ ("time" BETWEEN 1 AND 100) │ MyTable    │ WHERE   │
└────────────────────────────┴────────────┴─────────┘

-- Detailed parsing of a BETWEEN condition
SELECT * FROM parse_where_detailed('SELECT * FROM MyTable WHERE time BETWEEN 1 AND 100');
┌─────────────┬───────────────┬─────────┬────────────┬─────────┐
│ column_name │ operator_type │  value  │ table_name │ context │
│   varchar   │    varchar    │ varchar │  varchar   │ varchar │
├─────────────┼───────────────┼─────────┼────────────┼─────────┤
│ time        │ >=            │ 1       │ MyTable    │ WHERE   │
│ time        │ <=            │ 100     │ MyTable    │ WHERE   │
└─────────────┴───────────────┴─────────┴────────────┴─────────┘