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;
>
