Notice that the view isn't bound to the numbers table or its x column
until it's actually used in the SELECT query. [...] This might also
make it possible for a table-valued function to recursively invoke
itself, provided that it has a basis case to avoid infinite descent.
We already have recursive invocation with common table expressions.
Nevertheless, here's another way to look at it:
CREATE VIEW range AS
SELECT start
WHERE CASE WHEN step > 0 THEN start <= stop ELSE start >= stop END
UNION ALL range(start + step, stop, step)
PARAMETERS (start, stop, step DEFAULT 1 CHECK (step != 0));
Here I say "range(...)" as shorthand for "SELECT * FROM range(...)", by
analogy with IN and table-valued functions. With the above, once start
exceeds stop, the engine would have to optimize out the infinite
sequence of UNION ALL against guaranteed-empty results.
It would make sense for table-valued functions to be usable as
expressions, provided that they return exactly one row and one column.
CREATE VIEW double AS SELECT arg * 2 PARAMETERS (arg);
SELECT x, double(x) FROM range(1, 10);
How about table-valued functions as row values, provided they return
exactly one row?
CREATE TABLE foo (a, b, c);
CREATE VIEW multiples AS SELECT x, x * 2, x * 3 PARAMETERS (x);
UPDATE foo SET (a, b, c) = multiples(5) WHERE a = 0;
Table-valued functions are already permitted to be the right-hand of an
IN operator, so these new table-valued functions should be no different,
provided they return exactly one column.
CREATE VIEW multiples AS
VALUES (x)
UNION ALL
VALUES (x * 2)
UNION ALL
VALUES (x * 3)
PARAMETERS (x);
SELECT * FROM foo WHERE (a, b, c) IN multiples(a);
--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
