SQLError: no such table: generate_series

[Jeremy Whetzel]

Hello!

I'm trying to use generate_series() to build a table of integers.  In SQLite, this works:

SQLite version 3.39.4 2022-09-29 15:55:41
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(1, 10);
1
2
3
4
5
6
7
8
9
10
sqlite>

In apsw, it gives an error that there's no such table called generate_series:

> python -m apsw database.sqlite
SQLite version 3.39.4 (APSW 3.39.4.0)
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select value from generate_series(1, 10);
SQLError: no such table: generate_series
sqlite>

I'm new to apsw, so I'm not sure if I'm just missing a step or something, or if generate_series() actually isn't supported.  If it's not supported, I'd like to request support for it, please.

Thanks,

Jeremy

[Roger Binns]

On Thu, 17 Nov 2022, at 13:19, 'Jeremy Whetzel' via python-sqlite wrote:
> I'm trying to use generate_series() to build a table of integers. In
> SQLite, this works:

It is included in the SQLite shell, but is not part of SQLite itself.

> In apsw, it gives an error that there's no such table called generate_series:

... which is why you won't see it using anything except the SQLite shell.

> something, or if generate_series() actually isn't supported. If it's
> not supported, I'd like to request support for it, please.

https://github.com/rogerbinns/apsw/issues/380

It would need be defined in Python code, and would also make a good example.

In the mean time you can use a recursive expression. See

https://www.sqlite.com/series.html

Roger

[Jeremy Whetzel]

On Thursday, November 17, 2022 at 6:22:22 PM UTC-5 Roger Binns wrote:

> I'm trying to use generate_series() to build a table of integers. In
> SQLite, this works:

It is included in the SQLite shell, but is not part of SQLite itself.

 

> In apsw, it gives an error that there's no such table called generate_series:

... which is why you won't see it using anything except the SQLite shell.

Ah, sort of like the dot commands!  I didn't realize that was the case with generate_series(), too.  Good to know.

 

> something, or if generate_series() actually isn't supported. If it's
> not supported, I'd like to request support for it, please.

https://github.com/rogerbinns/apsw/issues/380

It would need be defined in Python code, and would also make a good example.

In the mean time you can use a recursive expression. See

https://www.sqlite.com/series.html

Thank you for this!   This will work just fine.

Thanks for your help, Roger!

Jeremy

[Roger Binns]

The next release of APSW (in the next few weeks) now includes an easy wrapper to turn your own functions into tables in the same way that generate_series works, and two implementations of generate_series.  One behaves exactly as SQLite's implementation does and one behaves how the other databases do it.  (The differences are around negative steps, how many parameters have to be supplied, and error handling.)

Roger

[Jeremy Whetzel]

That's fantastic!  Thank you so much!