On 07/14/18 20:09, E.Pasma wrote:
The new example is clear, and therefore also raises a question:
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.otherName;
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
The function "older" is used here in the SELECT part. But as a
table-valued function I think it must be in the FROM part:
SELECT t1.name AS name, t2.name AS otherName, older --column name
FROM people AS t1, people AS t2, older(t1.name, t2.name)
WHERE t1.name != t2.name;
My question is: do you envisage a regular function, returning a single
value, or a table-valued function. (that behaves as a table, even the
parameters may be passed as predicates in the WHERE clause)?
Either! If it returns a single row and a single column, let it be a
regular function. Or if it returns a single row, let it be a row value.
Or multiple rows (or one or even zero, of course), let it be a table.
I think the usage should dictate; no need to declare in advance.
If the subject is "defining regular functions directly in SQL" then
CREATE FUNCTION may be considered. This seems easier to understand and
develop. but in the end it can have an enormous impact, like in
postgres sql-createfunction.html
<https://www.postgresql.org/docs/9.1/static/sql-createfunction.html>
I'm trying to minimize the new syntax in my proposal. CREATE FUNCTION
seems too large for SQLite as it stands, though perhaps a small subset
could be adopted. I just seen an opportunity to get much of the same
capability with a small addition to existing syntax.
For the sudoku-solver you do use the table-valued function notation:
SELECT s FROM x(sud) WHERE NOT ind;
and the function should possibly return multiple rows here. Like if a
sudoku is ambiguous or if changing the WHERE clause for development.
Right. I don't see why it can't go both ways.
The idea would help me. I wonder if it can also be applied in inline
views.
"Inline view" is a phrase I'm not familiar with. Your example shows a
nested query, augmented with PARAMETERS. I suppose it could be called
an inline view, though.
That could be imagined if PARAMETERS is written after the query
like was an earlier idea.
Right, that's what I first suggested, associating PARAMETERS with SELECT
rather than with CREATE VIEW or WITH common-table-expression. So
perhaps that is a more powerful way to do things after all.
SELECT name, nolder
FROM people
LEFT JOIN (--inline view
SELECT COUNT(*)
FROM people p2
WHERE age>parameter.age
PARAMETERS (age)
) USING (age)
;
Huh! That's neat. For reference, here's how to do the same right now:
CREATE TABLE people (name, age);
INSERT INTO people VALUES ('Bob', 33), ('Jen', 19), ('Liz', 30);
SELECT people.name AS name
, count(other.name) AS nolder
FROM people
LEFT JOIN people AS other ON (other.age > people.age)
GROUP BY people.name;
name nolder
---------- ----------
Bob 0
Jen 2
Liz 1
--
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
