Table-valued functions can only be defined within compiled application
code by means of virtual tables. I suggest extending SQLite views and
common table expressions to be the vehicle for defining table-valued
functions directly in SQL.
A view or common table expression that references nonexistent columns is
very similar to a table-valued function, if only there were a way to
bind the nonexistent columns to function arguments. I thought of
several ways to go about doing this, which I'll list below. First,
here's the current syntax for which I will be providing alternatives:
CREATE TABLE numbers (x);
INSERT INTO numbers VALUES (1), (2), (3);
CREATE VIEW double AS SELECT x * 2 FROM numbers;
SELECT * FROM numbers, double;
My favored approach is to let the SELECT clause of the view or common
table expression be followed by a new "PARAMETERS (param1, ..., paramN)"
clause, where "param1" through "paramN" are the parameter names, listed
in the order their values will be supplied in the table-valued function
argument list.
CREATE VIEW double AS SELECT arg * 2 PARAMETERS (arg);
SELECT * FROM numbers, double(x);
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 makes it possible to
use a single view with many data sources, plus decoupling the operations
performed in the view from the data source can make the code clearer by
not forcing the reader to digest both at the same time. 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.
A second approach is to let table-valued function invocation supply
explicit parameter names for each argument:
CREATE VIEW double AS SELECT arg * 2;
SELECT * FROM numbers, double(x AS arg);
This has the advantage of letting the arguments appear in any order, but
the extra verbosity may be unwelcome. This approach can be combined
with the previous approach to allow optional reordering, plus it could
be used with regular virtual table-valued functions as well.
I'm not sure this is actually a useful feature though, unless someone
has a table-valued function with a large number parameters. However, it
does suggest the possibility of a new table-valued function being able
to supply default values for unbound parameters:
CREATE VIEW scale AS SELECT value * scalar
PARAMETERS (value, scalar DEFAULT 2)
SELECT * FROM numbers, scale(x);
SELECT * FROM numbers, scale(x, 3);
SELECT * FROM numbers, scale(3 AS scalar, x);
SELECT * FROM numbers, scale(3 AS scalar, x AS value);
We can get arbitrarily fancy by permitting more column constraints
within the PARAMETERS clause. I don't know if this is a good thing.
Here is a third approach which doesn't involve any syntactic changes.
Create a function "arg(n)" that returns the value of the nth argument.
CREATE VIEW double AS SELECT arg(1) * 2;
SELECT * FROM numbers, double(x);
I will list one more approach for the sake of completeness, but I don't
recommend it. Mirror virtual table-valued functions by having the
parameters be defined as HIDDEN columns:
CREATE VIEW double AS SELECT arg * 2, arg HIDDEN;
SELECT * FROM numbers, double(x);
I find this to be confusing, though a slight improvement would be to say
PARAMETER instead of HIDDEN. Either way, there is a compatibility
problem because current behavior is to treat HIDDEN or PARAMETER as a
column alias. To resolve the ambiguity, it would be necessary to
recognize HIDDEN or PARAMETER as a keyword only when the expression is a
simple term (no math) that is otherwise an unbound column. Messy.
Throughout this email I've tried to distinguish between parameters and
arguments, but it's a really fine point. Parameters are names whereas
arguments are values. I'm not sure this distinction is important, but I
went with it anyway.
I used views for my syntax examples, but I'd also like to see this work
for common table expressions.
WITH double AS (SELECT arg * 2 PARAMETERS (arg))
SELECT * FROM numbers, double(x);
--
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
