def test_with(self):
sub_query = (Query.from_(self.table_efg).select('fizz'))
test_query = (Query.with_(sub_query, "an_alias").from_(
AliasedQuery("an_alias")).select('*'))
self.assertEqual(
'WITH an_alias AS (SELECT "fizz" FROM "efg") SELECT * FROM an_alias',
str(test_query))
def test_select_from_with_returning(self):
sub_query = PostgreSQLQuery.into(
self.table_abc).insert(1).returning('*')
test_query = (Query.with_(sub_query, "an_alias").from_(
AliasedQuery("an_alias")).select("*"))
self.assertEqual(
'WITH an_alias AS (INSERT INTO "abc" VALUES (1) RETURNING *) SELECT * FROM an_alias',
str(test_query))
def test_join_with_with(self):
sub_query = (Query.from_(self.table_efg).select('fizz'))
test_query = (Query.with_(sub_query, "an_alias").from_(
self.table_abc).join(AliasedQuery('an_alias')).on(
AliasedQuery('an_alias').fizz == self.table_abc.buzz).select(
'*'))
self.assertEqual(
'WITH an_alias AS (SELECT "fizz" FROM "efg") '
'SELECT * FROM "abc" JOIN an_alias ON "an_alias"."fizz"="abc"."buzz"',
str(test_query))
def test_with_column_recursive(self):
sub_query = (Query.from_(self.table_efg).select("fizz").union(
Query.from_("an_alias").select("fizz")))
test_query = (Query.with_(sub_query, "an_alias", Field("fizz")).from_(
AliasedQuery("an_alias")).select("*"))
self.assertEqual(
'WITH RECURSIVE an_alias("fizz") AS ((SELECT "fizz" FROM "efg") UNION (SELECT "fizz" FROM "an_alias")) SELECT * FROM an_alias',
str(test_query),
)
def test_with_more_than_one(self):
s1 = Query.from_(self.table_efg).select("fizz")
s2 = Query.from_("a1").select("foo")
a1 = AliasedQuery("a1", s1)
a2 = AliasedQuery("a2", s2)
test_query = (Query.with_(s1, "a1").with_(
s2, "a2").from_("a1").from_("a2").select(a1.fizz, a2.foo))
self.assertEqual(
'WITH a1 AS (SELECT "fizz" FROM "efg") ,a2 AS (SELECT "foo" FROM "a1") SELECT "a1"."fizz","a2"."foo" FROM "a1","a2"',
str(test_query),
)
def test_update_with_statement(self):
table_efg = Table('efg')
sub_query = Query.from_(table_efg).select("fizz")
an_alias = AliasedQuery("an_alias")
q = (Query.with_(
sub_query, "an_alias").update(self.table_abc).from_(an_alias).set(
self.table_abc.lname, an_alias.long_name).where(
self.table_abc.comp.eq(an_alias.alias_comp)))
self.assertEqual(
'WITH an_alias AS (SELECT "fizz" FROM "efg") '
'UPDATE "abc" SET "lname"="an_alias"."long_name" FROM an_alias '
'WHERE "abc"."comp"="an_alias"."alias_comp"', str(q))