GENERATE_SERIES is not available in the command-line shell

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

GENERATE_SERIES is not available in the command-line shell

Digital Dog
Hi!

In the docs here
https://www.sqlite.org/series.html
there's this statement "The generate_series(START,END,STEP) table-valued
function is a loadable extension included in the SQLite source tree, **and
compiled into the command-line shell.**" Unfortunately in version 3.30.1 on
Windows downloaded from SQLite.org it's not available by default.
Is it going to be fixed in 3.31?
Thanks!

C:\>sqlite3
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT value FROM generate_series(5,50);
Error: no such table: generate_series
sqlite>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: GENERATE_SERIES is not available in the command-line shell

Richard Hipp-3
On 1/21/20, Digital Dog <[hidden email]> wrote:
> Hi!
>
> In the docs here
> https://www.sqlite.org/series.html
> there's this statement "The generate_series(START,END,STEP) table-valued
> function is a loadable extension included in the SQLite source tree, **and
> compiled into the command-line shell.**" Unfortunately in version 3.30.1 on
> Windows downloaded from SQLite.org it's not available by default.
> Is it going to be fixed in 3.31?

I'll fix the documentation, so that it doesn't say that any more, if
that is what you mean by "fixed".  :-)

You can use a recursive common table expression in place of
generated_series().  Like this:

WITH generate_series(value) AS (
  VALUES($START)
  UNION ALL
  SELECT value + $STEP FROM generate_series
    WHERE value $ STEP <= $STOP
)
SELECT value FROM generate_series;

This is more typing, I know.  But it is the official SQL standard way
of getting an ascending list of integers.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: GENERATE_SERIES is not available in the command-line shell

Digital Dog
> > Is it going to be fixed in 3.31?
>
> I'll fix the documentation, so that it doesn't say that any more, if
> that is what you mean by "fixed".  :-)
>
> Hoped to have it compiled in by default :-) It'd be nice addition... Come
on, typical Linux or Windows has multiple gigabytes of memory... I'm not
talking about enabling it by default for resource-constrained builds.

Thanks for the response anyway.


> You can use a recursive common table expression in place of
> generated_series().  Like this:
>
> WITH generate_series(value) AS (
>   VALUES($START)
>   UNION ALL
>   SELECT value + $STEP FROM generate_series
>     WHERE value $ STEP <= $STOP
> )
> SELECT value FROM generate_series;
>
> This is more typing, I know.  But it is the official SQL standard way
> of getting an ascending list of integers.
>
>
I would prefer more PostgreSQL'ish way of accomplishing the same :-)
It's crazy useful for ad-hoc one-liner queries to generate lots of data.
Have a good day!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users