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.

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

Reply via email to