On 07/07/18 19:47, E.Pasma wrote:
But the ideas allow a parameter name to be identical to a column name,
which must be an error.
While I might prefer that to be the case, it's actually not. We do not
have the ability for column expressions to reference other columns, so
there is no ambiguity even if a parameter were to be named the same as a
column. It's also currently permissible for multiple columns to have
the same name.
CREATE TABLE x (a, b);
INSERT INTO x VALUES (1, 2), (3, 4);
CREATE VIEW y AS SELECT a, b AS a FROM x;
CREATE VIEW z (a, a) AS SELECT * FROM x;
SELECT a, b AS a FROM x;
a a
---------- ----------
1 2
3 4
SELECT * FROM y;
a a:1
---------- ----------
1 2
3 4
SELECT * FROM z;
a a:1
---------- ----------
1 2
3 4
The handling varies a little bit between bare SELECT statements and
SELECTs wrapped in views. The latter appends a suffix to identical
column names, but the former does not.
I think the only rule would be that no two parameters can have the same
name.
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 did mention HIDDEN as an option in my original post, though not with
the syntax you suggest:
http://sqlite.1065341.n5.nabble.com/Idea-defining-table-valued-functions-directly-in-SQL-td102368.html
At the time I wasn't considering the optional parenthesized output
column-name list following the view name, since frankly I never use it.
I was thinking only of the SELECT statement, which also supplies the
output column names by means of AS clauses, or defaulting to using the
input expressions themselves as the names. Thus I allowed HIDDEN as a
column modifier in the SELECT clause, roughly copying the way virtual
table-valued functions currently work. (I also suggested saying
PARAMETER instead of HIDDEN to be a bit clearer about its function.)
If I understand you correctly, your idea is essentially to take this
approach but move the HIDDEN columns (i.e., parameters) from the SELECT
statement to the output column name list.
I have two issues with this. One is the fact that the output column
name list is optional, but now it would become required, at the cost of
the user having to retype the output column names even in cases where
the default is already good, or even if the user would have preferred to
use AS to name the output columns (which is what I do).
The second issue is one I had with my own idea as well, though I was
inarticulate about it. Whether the parameters are listed in the output
column name list or in the output column expression list, either way
we're interspersing outputs with inputs.
All that said, I do prefer your idea over mine. Having the parameter
names in the same list as the output column names may be mildly
confusing though overall okay with me, but I find my original idea of
having the parameter names in the same list as the output column
expressions to be off-the-charts weird, which is why I said in my
original email that I do not recommend using HIDDEN.
The bottom line for me is that at this point, my preference still goes
to the following:
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);
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.
That's really not what I had in mind. I wasn't thinking of FROM clauses
outside the view, rather those inside.
CREATE TABLE people (name, age);
INSERT INTO people VALUES ('Bob', 33), ('Jen', 19), ('Liz', 30);
CREATE VIEW older PARAMETERS (name, otherName) AS
SELECT t1.age > t2.age AS older
FROM people AS t1 WHERE t1.name = parameters.name
, people AS t2 WHERE t2.name = parameters.name;
SELECT t1.name AS name
, t2.name AS otherName
, older(t1.name, t2.name)
FROM people AS t1
, people AS t2
WHERE t1.name != t2.name;
name otherName older
---- --------- -----
Bob Jen 1
Bob Liz 1
Jen Bob 0
Jen Liz 0
Liz Bob 0
Liz Jen 1
SELECT t1.name AS senior
, t2.name AS junior
FROM people AS t1
, people AS t2
WHERE older(t1.name, t2.name);
senior junior
------ ------
Bob Jen
Bob Liz
Liz Jen
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;
Hmm, that's something I've not considered before: identifying parameters
as such everywhere they are used, rather than only in some declaration.
However, even though this works as a function, its usage won't resemble
other table-valued functions. Arguments aren't explicitly "passed" to
the function whereupon they are bound to parameters. Rather, this
binding is implied by automatically matching up names from elsewhere.
I fear the precise semantics might be too hard to define and understand.
An interface resembling more traditional function calls would be
easier to deal with and probably just as powerful in the end.
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;
How about this?
CREATE VIEW sudsol PARAMETERS (sud) AS
--
WITH 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) PARAMETERS (sud) AS (
SELECT sud
, instr(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(sud)
, digits AS z
WHERE ind
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 s FROM x(sud) WHERE NOT ind;
SELECT sudsol(
'53.'||'.7.'||'...'
|| '6..'||'195'||'...'
|| '.98'||'...'||'.6.'
|| '8..'||'.6.'||'..3'
|| '4..'||'8.3'||'..1'
|| '7..'||'.2.'||'..6'
|| '.6.'||'...'||'28.'
|| '...'||'419'||'..5'
|| '...'||'.8.'||'.79');
Rather than empower x to directly see sudsol's parameter, I declare both
sudsol and x to be table-valued functions, then I pass the argument
(terminology: parameter value) to each invocation of x. This is more
explicit and requires less magic on the part of SQLite to figure out
where everything is coming from when faced with nested functions.
Also, witness my lame attempt to format the input. :^)
By the way, I'm unclear why it's okay to simply say "lp" in the above
indexing expressions, rather than "lp.lp" which is what I believe is meant.
--
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
