Re: [sqlite] Idea: defining table-valued functions directly in SQL

[E.Pasma]

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   Jen1
> Bob   Liz1
> Jen   Bob0
> Jen   Liz0
> Liz   Bob0
> Liz   Jen1

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 
 

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

Re: [sqlite] SQLite .dump

[dmp]

Simon Slavin wrote:
> I'm sorry, but I don't see a question in your post.

As intended. It was just a statement based on observation,
with regard to SQLite .dump, my GUI dump, and other database
dump outputs. MySQL also uses a short version without
specifying column names, but does quotes identifiers.

Warren Young wrote:
> and using the database's
> identifier quoting character.

> It does that at need already:

> sqlite> create table "x y" ("a b" INTEGER);
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE IF NOT EXISTS "x y" ("a b" INTEGER);
> COMMIT;

Seems only when the initial dll specified.

sqlite> create table x (a INTEGER);
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE x (a INTEGER);
COMMIT;

There is no intention to indicate a bug, or other aspect
about a deficiency in SQLite .dump.

The reason my tool provides columns is because the
dump is made to allow users to selectively save data
from only the specified columns.

The reason I always quote identifiers, is because people
do stuff like this for names, "keY_cOlumn2".

If that was not quoted in dml then it might be interpreted
as KEY_COLUMN2 for example in some databases, and then throw
an error.

NO SUCH COLUMN.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users