CORRECTION: It seems one of the two options I've mentioned earlier, namely the CREATE TABLE AS SELECT... does not actually work on the back of a WITH clause. The other option still do, but this request has more appeal now.
On 2015-08-17 07:24 PM, R.Smith wrote: > > > On 2015-08-17 05:44 PM, John McKown wrote: >> I use both SQLite3 and PostgreSQL. One thing that would be really useful >> for me in SQLite3 which is in PostgreSQL is the INTO phrase. The >> PostgreSQL >> documentation this is here: >> http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html > > Hi John, firstly I agree that it is useful, but you do know it is > already possible with two other SQLite operations - namely, if the > table already exists, you can just do the INTO like this: > > WITH RECURSIVE generate AS > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > seqno<??limit??) > INSERT INTO table_of_integers SELECT seqno FROM generate ORDER BY seqno; > > > and, secondly, if the table does not exist yet, you can do this: > > WITH RECURSIVE generate AS > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > seqno<??limit??) > CREATE TABLE table_of_integers AS SELECT seqno FROM generate ORDER BY > seqno; > > > I realize that you probably know these methods well and have some kind > of reason to prefer the INTO done as per your example - and, If it > offers something more than the above statements, I (for one) would > like to know the detail of it if you don't mind sharing. > > Thanks! > Ryan > > >> >> The main reason that I could use this would be in conjunction with >> the WITH >> clause. A minor example would be: >> >> WITH RECURSIVE generate AS >> ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE >> seqno<??limit??) >> SELECT seqno FROM generate INTO table_of_intergers ORDER BY seqno; >> >> This could also solve the "problem" that another person had, wanting a >> sorted sequence of random numbers: >> >> WITH RECURSIVE generate AS >> ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE >> seqno<<??number of random number??>), >> random_number_generator >> ( SELECT RANDOM() AS random_number FROM generate) >> SELECT random_number FROM random_number_generator ORDER BY >> random_number; >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
