How to specify column names for a table alias

[google...@sql-workbench.net]

Hello,

I'm trying to integrate Postgres' generate_series() into my query. I need to join to the result of the function to another table. Something like:

select dates.dt, x.*
from generate_series(date '2015-01-01', current_date, interval '1' day) as dates(dt)
  left join x on x.some_column = dates.dt;

But I don't know how I can specify the column name in the "AS .." part.

My QueryDSL (SQL) currently looks like this:

Expression<Date> startDate = SQLExpressions.datetrunc(DatePart.year, SQLExpressions.addYears(DateExpression.currentDate(Date.class), -2));
RelationalFunctionCall<java.sql.Date> dateRange = SQLExpressions.relationalFunctionCall(java.sql.Date.class, "generate_series", startDate, DateExpression.currentDate(), "interval '1' month");

SQLQuery<Object[]> query = factory.
    select(SQLExpressions.all).
    from(dateRange.as("dates"));

How can I extend the as("dates") to include a column name?

Thanks in advance.

[google...@sql-workbench.net]

I thought I can do this with a CTE, but for some reason I can't specify a CTE with just one column.

The following will not compile:

DatePath<Date> dt = Expressions.datePath(Date.class, "dt");

SQLQuery query = factory.query().
    with(dates, dt).as(
        select(SQLExpressions.all).
        from(dateRange)
    ).
    select(dt).
    from(dates);

I can however define a CTE with two columns without problems:

DatePath<Date> dt = Expressions.datePath(Date.class, "dt");
NumberPath<Integer> dummy = Expressions.numberPath(Integer.class, "dummy");

SQLQuery query = factory.query().
    with(dates, dt, dummy).as(
        select(SQLExpressions.all, Expressions.ONE).
        from(dateRange)
    ).
    select(dt).
    from(dates);

So how could I define a CTE that defines the column name for just one column?

I also don't know how I can specify a parameter of type "interval" for the function.

SQLExpressions.relationalFunctionCall(java.sql.Date.class, "generate_series", startDate, DateExpression.currentDate(), "interval '1' month");


results in:

generate_series(date_trunc('year',current_date + interval '-2 years'), current_date, 'interval ''1'' month'))

which is wrong because the third parameter would need to be passed without single quotes:

generate_series(date_trunc('year',current_date + interval '-2 years'), current_date, interval '1' month))