JSQL Parser - Info on parsing functions

Question

Can JSQLParser differentiate between View/Function/Table in a SELECT query ?

If for example, executing a function in the following ways: select * from public.new(10); select public.new(10);

Is it possible for JSQL to figure out that it is executing a function and return that information?

Answer 1

The answer is: it depends.

JSqlParser is only a parser and does not have information about the database schema. In some databases parameterless functions are allowed to be called without parenthesis, e.g. select NOW (hope that is indeed a function ;)). In this case NOW would be accepted as a column name.

But JSqlParser supports parameterized functions, e.g. select testfunc(param1). In this case it will be accepted as a function.

Syntactically the usage of view and table is identical and JSqlParser is not able to differ between those. The view name would be accepted as a table name.

To get a differentiation:

1. first you would let JSqlParser parse your statement
2. extract all column names, table names, function names (a good start here is the TableNameFinder utility of JSqlParser)
3. get the final type you need to check it with your database schema

So here is a little example for point 1 and 2:

Statement statement = CCJSqlParserUtil.parse("select myfunc(5), now from public.new(10), mytable");

TablesNamesFinder tablesNamesFinder = new TablesNamesFinder() {
    @Override
    public void visit(Column tableColumn) {
        System.out.println("column = " + tableColumn);
    }

    @Override
    public void visit(Function function) {
        System.out.println("function = " + function.getName());
        super.visit(function); 
    }

    @Override
    public void visit(Table tableName) {
        System.out.println("table = " + tableName.getFullyQualifiedName());
        super.visit(tableName); 
    }

    @Override
    public void visit(TableFunction valuesList) {
        System.out.println("table function = " + valuesList.getFunction().getName());
        super.visit(valuesList); 
    }
};

System.out.println("all extracted tables=" + tablesNamesFinder.getTableList(statement));

and the result is:

function = myfunc
column = now
table function = public.new
table = mytable
all extracted tables=[mytable]