def compile_set_expr_postgres(compile, expr, state):
if expr.order_by is not Undef:
# The following statement breaks in postgres:
# SELECT 1 AS id UNION SELECT 1 ORDER BY id+1
# With the error:
# ORDER BY on a UNION/INTERSECT/EXCEPT result must
# be on one of the result columns
# So we transform it into something close to:
# SELECT * FROM (SELECT 1 AS id UNION SELECT 1) AS a ORDER BY id+1
# Build new set expression without arguments (order_by, etc).
new_expr = expr.__class__()
new_expr.exprs = expr.exprs
new_expr.all = expr.all
# Make sure that state.aliases isn't None, since we want them to
# compile our order_by statement below.
no_aliases = state.aliases is None
if no_aliases:
state.push("aliases", {})
# Build set expression, collecting aliases.
set_stmt = SQLRaw("(%s)" % compile_set_expr(compile, new_expr, state))
# Build order_by statement, using aliases.
state.push("context", COLUMN_NAME)
order_by_stmt = SQLRaw(compile(expr.order_by, state))
state.pop()
# Discard aliases, if they were not being collected previously.
if no_aliases:
state.pop()
# Build wrapping select statement.
select = Select(SQLRaw("*"),
tables=Alias(set_stmt),
limit=expr.limit,
offset=expr.offset,
order_by=order_by_stmt)
return compile_select(compile, select, state)
else:
return compile_set_expr(compile, expr, state)
def compile_set_expr_oracle(compile, expr, state):
if isinstance(expr, Minus):
# Build new set expression without arguments (order_by, etc).
new_expr = expr.__class__()
new_expr.exprs = expr.exprs
new_expr.all = expr.all
if expr.order_by is not Undef:
# Make sure that state.aliases isn't None, since we want them to
# compile our order_by statement below.
no_aliases = state.aliases is None
if no_aliases:
state.push("aliases", {})
aliases = {}
for subexpr in expr.exprs:
if isinstance(subexpr, Select):
columns = subexpr.columns
if not isinstance(columns, (tuple, list)):
columns = [columns]
else:
columns = list(columns)
for i, column in enumerate(columns):
if column not in aliases:
if isinstance(column, Column):
aliases[column] = columns[i] = Alias(column)
elif isinstance(column, Alias):
aliases[column.expr] = column
subexpr.columns = columns
aliases.update(state.aliases)
state.aliases = aliases
aliases = None
set_stmt = SQLRaw('(%s)' % compile(expr.exprs, state, join=expr.oper))
if expr.order_by is not Undef:
# Build order_by statement, using aliases.
state.push("context", COLUMN_NAME)
order_by_stmt = SQLRaw(compile(expr.order_by, state))
state.pop()
else:
order_by_stmt = Undef
# Build wrapping select statement.
select = Select(SQLRaw("*"),
tables=Alias(set_stmt),
limit=expr.limit,
offset=expr.offset,
order_by=order_by_stmt)
return compile_select(compile, select, state)
return compile_set_expr(compile, expr, state)
def compile_set_expr_postgres(compile, expr, state):
if expr.order_by is not Undef:
# The following statement breaks in postgres:
# SELECT 1 AS id UNION SELECT 1 ORDER BY id+1
# With the error:
# ORDER BY on a UNION/INTERSECT/EXCEPT result must
# be on one of the result columns
# So we transform it into something close to:
# SELECT * FROM (SELECT 1 AS id UNION SELECT 1) AS a ORDER BY id+1
# Build new set expression without arguments (order_by, etc).
new_expr = expr.__class__()
new_expr.exprs = expr.exprs
new_expr.all = expr.all
# Make sure that state.aliases isn't None, since we want them to
# compile our order_by statement below.
no_aliases = state.aliases is None
if no_aliases:
state.push("aliases", {})
# Build set expression, collecting aliases.
set_stmt = SQLRaw("(%s)" % compile_set_expr(compile, new_expr, state))
# Build order_by statement, using aliases.
state.push("context", COLUMN_NAME)
order_by_stmt = SQLRaw(compile(expr.order_by, state))
state.pop()
# Discard aliases, if they were not being collected previously.
if no_aliases:
state.pop()
# Build wrapping select statement.
select = Select(SQLRaw("*"), tables=Alias(set_stmt), limit=expr.limit,
offset=expr.offset, order_by=order_by_stmt)
return compile_select(compile, select, state)
else:
return compile_set_expr(compile, expr, state)