You can get the same effect from postgres with:
<pre>
WITH RECURSIVE a AS ( SELECT 1 UNION ALL SELECT * FROM a ) SELECT * FROM a;
</pre>
SQLite just doesn't require the RECURSIVE keyword.
It's a statement that returns an infinite number of rows. See point 4 here:
[https://www.sqlite.org/security.html#untrusted_sql_inputs]
for ways for an application that accepts untrusted SQL to defend against the DOS attack..