Edit on GitHub

# sqlglot.optimizer.eliminate_ctes

View Source

     1from sqlglot.optimizer.scope import Scope, build_scope
     2
     3
     4def eliminate_ctes(expression):
     5    """
     6    Remove unused CTEs from an expression.
     7
     8    Example:
     9        >>> import sqlglot
    10        >>> sql = "WITH y AS (SELECT a FROM x) SELECT a FROM z"
    11        >>> expression = sqlglot.parse_one(sql)
    12        >>> eliminate_ctes(expression).sql()
    13        'SELECT a FROM z'
    14
    15    Args:
    16        expression (sqlglot.Expression): expression to optimize
    17    Returns:
    18        sqlglot.Expression: optimized expression
    19    """
    20    root = build_scope(expression)
    21
    22    if root:
    23        ref_count = root.ref_count()
    24
    25        # Traverse the scope tree in reverse so we can remove chains of unused CTEs
    26        for scope in reversed(list(root.traverse())):
    27            if scope.is_cte:
    28                count = ref_count[id(scope)]
    29                if count <= 0:
    30                    cte_node = scope.expression.parent
    31                    with_node = cte_node.parent
    32                    cte_node.pop()
    33
    34                    # Pop the entire WITH clause if this is the last CTE
    35                    if with_node and len(with_node.expressions) <= 0:
    36                        with_node.pop()
    37
    38                    # Decrement the ref count for all sources this CTE selects from
    39                    for _, source in scope.selected_sources.values():
    40                        if isinstance(source, Scope):
    41                            ref_count[id(source)] -= 1
    42
    43    return expression

def eliminate_ctes(expression): View Source

     5def eliminate_ctes(expression):
     6    """
     7    Remove unused CTEs from an expression.
     8
     9    Example:
    10        >>> import sqlglot
    11        >>> sql = "WITH y AS (SELECT a FROM x) SELECT a FROM z"
    12        >>> expression = sqlglot.parse_one(sql)
    13        >>> eliminate_ctes(expression).sql()
    14        'SELECT a FROM z'
    15
    16    Args:
    17        expression (sqlglot.Expression): expression to optimize
    18    Returns:
    19        sqlglot.Expression: optimized expression
    20    """
    21    root = build_scope(expression)
    22
    23    if root:
    24        ref_count = root.ref_count()
    25
    26        # Traverse the scope tree in reverse so we can remove chains of unused CTEs
    27        for scope in reversed(list(root.traverse())):
    28            if scope.is_cte:
    29                count = ref_count[id(scope)]
    30                if count <= 0:
    31                    cte_node = scope.expression.parent
    32                    with_node = cte_node.parent
    33                    cte_node.pop()
    34
    35                    # Pop the entire WITH clause if this is the last CTE
    36                    if with_node and len(with_node.expressions) <= 0:
    37                        with_node.pop()
    38
    39                    # Decrement the ref count for all sources this CTE selects from
    40                    for _, source in scope.selected_sources.values():
    41                        if isinstance(source, Scope):
    42                            ref_count[id(source)] -= 1
    43
    44    return expression

Remove unused CTEs from an expression.

###### Example:

>     >>> import sqlglot
>     >>> sql = "WITH y AS (SELECT a FROM x) SELECT a FROM z"
>     >>> expression = sqlglot.parse_one(sql)
>     >>> eliminate_ctes(expression).sql()
>     'SELECT a FROM z'

###### Arguments:

- expression (sqlglot.Expression): expression to optimize

###### Returns:

> sqlglot.Expression: optimized expression