Revisiting this topic...
On 06/10/18 08:04, [email protected] wrote:
* Perhaps move PARAMETERS before AS, which may make the syntax easier.
Like so?
CREATE VIEW double PARAMETERS (arg) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
This is a readability improvement because universally I see function
names and parameters defined before function bodies. Moving the
PARAMETERS to the left of AS also represents a conceptual shift from
PARAMETERS modifying SELECT (which is what I had in mind) to PARAMETERS
modifying CREATE VIEW (which could well be a better way to look at it).
Now, let's examine the common table expression variant. I'll repeat the
baseline syntax proposal from my original post:
WITH double AS (SELECT arg * 2 PARAMETERS (arg))
SELECT * FROM numbers, double(x);
Would your suggestion be the following?
WITH double PARAMETERS (arg) AS (SELECT arg * 2)
SELECT * FROM numbers, double(x);
Next, what is the interaction with an explicit column-name list? Does
the PARAMETERS clause come before or after that? Compare:
CREATE VIEW double (result) PARAMETERS (arg) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
WITH double (result) PARAMETERS (arg) AS (SELECT arg * 2)
SELECT * FROM numbers, double(x);
Versus:
CREATE VIEW double PARAMETERS (arg) (result) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
WITH double PARAMETERS (arg) (result) AS (SELECT arg * 2)
SELECT * FROM numbers, double(x);
I don't think there's any question the former is superior, but I bring
this up for two reasons. One, every syntax question needs an answer.
Two, I wish to highlight the fact that the (existing) syntax for
column-name list looks very much like what most languages use for a
parameter list, so there's potential confusion, hence the need for the
PARAMETERS token.
* I do agree that defining table-valued functions in these way can be
useful though; I have wanted to define views that take parameters
before, and was unable to.
I would love to be able to create functions without writing extensions
in C, plus this way functions won't require recursive invocation of
SQLite and won't have to be a barrier to the SQLite optimizer. Rather,
native functions would be inlined right into the bytecodes of whichever
queries use them.
* Another (separate) idea can be "CREATE FUNCTION name(args) AS
select_stmt;" to define your own function. [...] Both of these are
separate from table-valued functions (parameterized views) though.
Aside from the syntax, is this really a separate idea? What does this
do that views can't?
If you write "CREATE AGGREGATE FUNCTION" then the function name can be
used as a table name within the select_stmt.
I don't think I understand the part about letting the function name be
used as a table name. Parameters would already be bound, so there's no
need for a FROM clause to get at them. That's central to the concept of
parameters as explored by this email thread; I'm hunting for a practical
use for situations that would currently give a "no such column" error.
However, you bring up an interesting question, though it's a potential
issue whether or not the function is an aggregate function. What if a
parameter name happens to match a column name in one (or more) of the
tables being pulled in by a FROM clause? A table name qualifier is
needed to disambiguate. It could be the function name, though it could
be clearer to do like upsert ("excluded") and have a special token, e.g.
"parameters". (cf. https://sqlite.org/lang_UPSERT.html)
The above applies to parameterized views and common table expressions as
well, so replace "function name" with "view name" or "common table
expression name".
As for having to declare a function as an aggregate, I think that could
be inferred from the fact that the function uses aggregate functions on
its parameters. Maybe it would have to also not use GROUP BY on those
parameters, not sure there. What makes an aggregate function, anyway?
It always map multiple input rows to a single output row? Then does
that mean having LIMIT 1 would also make it an aggregate function? But
before we get bogged down in semantics, I ask whether or not this
distinction even matters.
--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
