Are there any database implementations that allow for tables that don't contain data but genera-CodePudding

I have an application that works well with database query outputs but now need to run with each output over a range of numbers. Sure, I could refactor the application to iterate over the range for me, but it would arguably be cleaner if I could just have a "table" in the database that I could CROSS JOIN with my normal query outputs. Sure, I could just make a table that contains a range of values, but that seems like unnecessary waste.

For example a "table" in a database that represents a range of values, say 0 to 999,999 in a column called "number" WITHOUT having to actually store a million rows, but can be used in a query with a CROSS JOIN with another table as though there actually existed such a table.

I am mostly just curious if such a construct exists in any database implementation.

CodePudding user response：

PostgreSQL has generate_series. SQLite has it as a loadable extension.

SELECT * FROM generate_series(0,9);

On databases which support recursive CTE (SQLite, PostgreSQL, MariaDB), you can do this and then join with it.

WITH RECURSIVE cnt(x) AS (
  VALUES(0)
  UNION ALL
  SELECT x 1 FROM cnt WHERE x < 1000000
)
SELECT x FROM cnt;

The initial-select runs first and returns a single row with a single column "1". This one row is added to the queue. In step 2a, that one row is extracted from the queue and added to "cnt". Then the recursive-select is run in accordance with step 2c generating a single new row with value "2" to add to the queue. The queue still has one row, so step 2 repeats. The "2" row is extracted and added to the recursive table by steps 2a and 2b. Then the row containing 2 is used as if it were the complete content of the recursive table and the recursive-select is run again, resulting in a row with value "3" being added to the queue. This repeats 999999 times until finally at step 2a the only value on the queue is a row containing 1000000. That row is extracted and added to the recursive table. But this time, the WHERE clause causes the recursive-select to return no rows, so the queue remains empty and the recursion stops.

CodePudding user response：

Looks like the answer to my question "Are there any database implementations that allow for tables that don't contain data but generate data upon query?" is yes. For example in sqlite there exists virtual tables: https://www.sqlite.org/vtab.html

In fact, it has the exact sort of thing I was looking for with generate_series: https://www.sqlite.org/series.html

CodePudding user response：

Generally speaking, this depends a lot on the database you're using. In SQLite, for example, you are going to generator a sequence from 1 to 100. You could code like this:

WITH basic(i) AS (
  VALUES(1)
),
seq(i) AS (
  SELECT i FROM basic
  UNION ALL
  SELECT i   1 FROM seq WHERE i < 100
)
SELECT * FROM seq;

Hope ring your bell.