Hello,
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)?
If the subject is "defining regular functions directly in SQL" then CREATE
FUNTION 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>
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.
The idea would help me. I wonder if it can also be applied in inline views.
That could be imagined if PARAMETERS is written after the query like was an
earlier idea.
SELECT name, nolder
FROM people
LEFT JOIN (--inline view
SELECT COUNT(*)
FROM people p2
WHERE age>parameter.age
PARAMETERS (age)
) USING (age)
;
E. Pasma
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
