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

Reply via email to