> 4 jul. 2018, Andy Goth:
> Revisiting this topic...
>
> On 06/10/18 08:04, sql...@zzo38computer.org 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.
Hello,
Not any question about what order looks superior.
But the ideas allow a parameter name to be identical to a column name, which
must be an error. So why not a single column list and a modifier to turn a
column into an argument?
CREATE VIEW double (result, arg HIDDEN) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
>
>> * 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)
>
This side idea is more powerful than just to disambiguate column names. It
tells me that a column is pulled from outside the view. An explicit parameter
list is not even needed. The column must only exist in the view specification.
CREATE VIEW double (arg, result) AS SELECT PARAMETERS.arg, PARAMETERS.arg * 2;
SELECT * FROM numbers, double ON arg=x;
A disadvantage is that this view behaves no longer purely relational. For
instance:
select * from double where arg=2;
2|4
select * from double where arg>=2;
select * from double where +arg=2;
select * from double where arg=NULL;
0|0
This behaviour is also intrinsic to user-defined functions, unless extremely
carefully programmed. The outcomes above are equivalent to what generate_series
does with these sort of predicates.
Not sure how bad this is when achievable in plain SQL.
I leave out the remainder of the message and give some personal motivation
instead. I want to make a view of the sudoku solver
https://www.sqlite.org/lang_with.html#sudoku
That becomes like below. I have in mind to combine it with a further view to
format the input or output.
Thanks, E.Pasma
CREATE VIEW sudsol(sud,sol) AS
WITH RECURSIVE
digits(z, lp) AS (
VALUES('1', 1)
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
x(s, ind) AS (
SELECT PARAMETERS.sud, instr(PARAMETERS.sud, '.')
UNION ALL
SELECT
substr(s, 1, ind-1) || z || substr(s, ind+1),
instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
FROM x, digits AS z
WHERE ind>0
AND NOT EXISTS (
SELECT 1
FROM digits AS lp
WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
OR z.z = substr(s, (((ind-1)/3) % 3) * 3
+ ((ind-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6, 1)
)
)
SELECT PARAMETERS.sud, s FROM x WHERE ind=0;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
