Get Non CTE expressions

[vedas2]

I have two questions:

1. Is is possible to get table name for a column if table alias is not used. I used following code to get table references

for select in node.find_all(exp.Select):
    for c in select.expressions:
        if c.key == "column":
            print(c.table)

This works for SQL where aliases are used such as select t.a, t.b from table as t. But If I have a query such as select a,b from table, is there a way to resolve table references?

2. node.find_all(exp.CTE) gives all CTEs of the the query. Is there any way to get the opposite? i.e non-CTEs only?

[georgesittas]

But If I have a query such as select a,b from table, is there a way to resolve table references?

Yes, you need to run the query through the optimizer and it'll resolve each column reference, qualifying it accordingly.

Is there any way to get the opposite? i.e non-CTEs only?

I'm not sure what "non-CTEs" means. If you don't want the CTEs of the query you can pop the "with" arg of each exp.Select expression in the AST of interest. Note that this will mutate the AST, so consider copying it first if you don't want that (or just use the transform method, since it automatically copies the input expression before transforming it).

[vedas2]

Yes, you need to run the query through the optimizer.

Can you provide an example or link if there is already one.

you can pop the "with" arg of each exp.Select

Again a small example would help.

[georgesittas]

Yep, here's a simple example from the README: https://github.com/tobymao/sqlglot#sql-optimizer.

And for the CTEs, I had something like the following in mind:

from sqlglot import exp, parse_one

expression = parse_one(<your_query>)

def remove_ctes(expression):
    if isinstance(expression, exp.Select) and expression.ctes:
        expression.args["with"].pop()
    return expression

print(expression.transform(remove_ctes).sql(pretty=True))

[vedas2]

Thankyou! It works!