<p dir="auto"><strong>Migrated issue, originally created by mike_solomon</strong></p>
<p dir="auto">The code below :</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import Table, Column, Integer, MetaData, select, and_, exists

metadata = MetaData()
foo = Table('foo', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('val', Integer))

bar = Table('bar', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('val', Integer))


first = select([bar.c.id.label('elt')]).where(bar.c.id == 0).\
          cte(name = 'first_row', recursive = True)
grow_me = first.alias(name = 'grow_me')
stmt = first.union_all(select([bar.c.val.label('next')]).\
     where(and_(grow_me.c.elt == bar.c.id,
                grow_me.c.elt != 1)))

print foo.delete().where(exists(select([stmt])))"><pre class="notranslate"><code class="notranslate">from sqlalchemy import Table, Column, Integer, MetaData, select, and_, exists

metadata = MetaData()
foo = Table('foo', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('val', Integer))

bar = Table('bar', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('val', Integer))


first = select([bar.c.id.label('elt')]).where(bar.c.id == 0).\
          cte(name = 'first_row', recursive = True)
grow_me = first.alias(name = 'grow_me')
stmt = first.union_all(select([bar.c.val.label('next')]).\
     where(and_(grow_me.c.elt == bar.c.id,
                grow_me.c.elt != 1)))

print foo.delete().where(exists(select([stmt])))
</code></pre></div>
<p dir="auto">generates the result below:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="DELETE FROM foo WHERE EXISTS (SELECT first_row.elt 
FROM first_row)"><pre class="notranslate"><code class="notranslate">DELETE FROM foo WHERE EXISTS (SELECT first_row.elt 
FROM first_row)
</code></pre></div>
<p dir="auto">I would have expected it to generate this code:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="DELETE FROM foo WHERE EXISTS (
WITH RECURSIVE first_row(elt) AS 
(SELECT bar.id AS elt 
FROM bar 
WHERE bar.id = :id_1 UNION ALL SELECT bar.val AS next 
FROM bar, first_row AS grow_me 
WHERE grow_me.elt = bar.id AND grow_me.elt != :elt_1)
 SELECT first_row.elt 
FROM first_row)"><pre class="notranslate"><code class="notranslate">DELETE FROM foo WHERE EXISTS (
WITH RECURSIVE first_row(elt) AS 
(SELECT bar.id AS elt 
FROM bar 
WHERE bar.id = :id_1 UNION ALL SELECT bar.val AS next 
FROM bar, first_row AS grow_me 
WHERE grow_me.elt = bar.id AND grow_me.elt != :elt_1)
 SELECT first_row.elt 
FROM first_row)
</code></pre></div>
<p dir="auto">The code above is legal SQL.  As a workaround for my project, I've created a simple string formatter that will insert the SELECT statement into the EXISTS statement, but it would be great if this could be done automatically.</p>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">apparently its all possible now.  might as well make a new syntax bonanza out of it:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="WITH upsert AS (
  UPDATE metric k SET k.count = k.count + 5
  WHERE event = &quot;foo&quot; AND interval = &quot;D&quot; and date = &quot;whatever&quot;
  RETURNING k.*
)
INSERT INTO metric (event, interval, date, count) 
SELECT (&quot;foo&quot;, &quot;D&quot;, &quot;whatever&quot;, 5)
WHERE NOT EXISTS (
  SELECT 1 FROM upsert
);"><pre class="notranslate"><code class="notranslate">WITH upsert AS (
  UPDATE metric k SET k.count = k.count + 5
  WHERE event = "foo" AND interval = "D" and date = "whatever"
  RETURNING k.*
)
INSERT INTO metric (event, interval, date, count) 
SELECT ("foo", "D", "whatever", 5)
WHERE NOT EXISTS (
  SELECT 1 FROM upsert
);
</code></pre></div>
<p dir="auto"><a href="http://www.postgresql.org/docs/9.1/static/sql-insert.html" rel="nofollow">http://www.postgresql.org/docs/9.1/static/sql-insert.html</a></p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/2551/ticket_2551.patch">ticket_2551.patch</a> | <a href="../wiki/imported_issue_attachments/2551/CTE.sqlalchemy">CTE.sqlalchemy</a></p>