Idea: defining table-valued functions directly in SQL

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
27 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Idea: defining table-valued functions directly in SQL

Andy Goth-3
Table-valued functions can only be defined within compiled application
code by means of virtual tables.  I suggest extending SQLite views and
common table expressions to be the vehicle for defining table-valued
functions directly in SQL.

A view or common table expression that references nonexistent columns is
very similar to a table-valued function, if only there were a way to
bind the nonexistent columns to function arguments.  I thought of
several ways to go about doing this, which I'll list below.  First,
here's the current syntax for which I will be providing alternatives:

CREATE TABLE numbers (x);
INSERT INTO numbers VALUES (1), (2), (3);
CREATE VIEW double AS SELECT x * 2 FROM numbers;
SELECT * FROM numbers, double;

My favored approach is to let the SELECT clause of the view or common
table expression be followed by a new "PARAMETERS (param1, ..., paramN)"
clause, where "param1" through "paramN" are the parameter names, listed
in the order their values will be supplied in the table-valued function
argument list.

CREATE VIEW double AS SELECT arg * 2 PARAMETERS (arg);
SELECT * FROM numbers, double(x);

Notice that the view isn't bound to the numbers table or its x column
until it's actually used in the SELECT query.  This makes it possible to
use a single view with many data sources, plus decoupling the operations
performed in the view from the data source can make the code clearer by
not forcing the reader to digest both at the same time.  This might also
make it possible for a table-valued function to recursively invoke
itself, provided that it has a basis case to avoid infinite descent.

A second approach is to let table-valued function invocation supply
explicit parameter names for each argument:

CREATE VIEW double AS SELECT arg * 2;
SELECT * FROM numbers, double(x AS arg);

This has the advantage of letting the arguments appear in any order, but
the extra verbosity may be unwelcome.  This approach can be combined
with the previous approach to allow optional reordering, plus it could
be used with regular virtual table-valued functions as well.

I'm not sure this is actually a useful feature though, unless someone
has a table-valued function with a large number parameters.  However, it
does suggest the possibility of a new table-valued function being able
to supply default values for unbound parameters:

CREATE VIEW scale AS SELECT value * scalar
                  PARAMETERS (value, scalar DEFAULT 2)
SELECT * FROM numbers, scale(x);
SELECT * FROM numbers, scale(x, 3);
SELECT * FROM numbers, scale(3 AS scalar, x);
SELECT * FROM numbers, scale(3 AS scalar, x AS value);

We can get arbitrarily fancy by permitting more column constraints
within the PARAMETERS clause.  I don't know if this is a good thing.

Here is a third approach which doesn't involve any syntactic changes.
Create a function "arg(n)" that returns the value of the nth argument.

CREATE VIEW double AS SELECT arg(1) * 2;
SELECT * FROM numbers, double(x);

I will list one more approach for the sake of completeness, but I don't
recommend it.  Mirror virtual table-valued functions by having the
parameters be defined as HIDDEN columns:

CREATE VIEW double AS SELECT arg * 2, arg HIDDEN;
SELECT * FROM numbers, double(x);

I find this to be confusing, though a slight improvement would be to say
PARAMETER instead of HIDDEN.  Either way, there is a compatibility
problem because current behavior is to treat HIDDEN or PARAMETER as a
column alias.  To resolve the ambiguity, it would be necessary to
recognize HIDDEN or PARAMETER as a keyword only when the expression is a
simple term (no math) that is otherwise an unbound column.  Messy.

Throughout this email I've tried to distinguish between parameters and
arguments, but it's a really fine point.  Parameters are names whereas
arguments are values.  I'm not sure this distinction is important, but I
went with it anyway.

I used views for my syntax examples, but I'd also like to see this work
for common table expressions.

WITH double AS (SELECT arg * 2 PARAMETERS (arg))
SELECT * FROM numbers, double(x);

--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Simon Slavin-3
On 9 Jun 2018, at 9:21pm, Andy Goth <[hidden email]> wrote:

> I suggest extending SQLite views and common table expressions to be the vehicle for defining table-valued functions directly in SQL.

I'll raise your bid.  I want computed columns (SQL SERVER), otherwise known as generated columns (MySQL).

        CREATE TABLE tempLog (
                datestamp TEXT COLLATE NOCASE PRIMARY KEY,
                centTemp REAL,
                fahrTemp AS (centTemp*9/5 + 32) )

I'm happy with another syntax as long as it does the same thing.

Niggle 1: Can a computed column refer to a column defined after it ?

Niggle 2: Should a computed value be stored with the other values or calculated when required ?

With regard to Niggle 2, some SQL variants support both answers.  SQL SERVER allows you to use PERSISTED after the calculation to say that you want calculations performed and stored on INSERT and UPDATE.  Whoever picked the word PERSISTED should be shot.  We hates it.  MySQL uses either VIRTUAL or STORED, defaulting to VIRTUAL.  I prefer those words.

This should obsolete indexes on expressions, though it probably wouldn't in SQLite 3 for historical compatibility reasons.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

David Burgess-2
Would schema defined functions fulfill the same requirement (e.g.
return val  = p1 || p2) ?

Computed columns would be of assistance to me, assuming that functions
are permitted. e.g.
full_account_number CHAR(16),
branch CHAR(4),
account_number CHAR(12),

full_account_number AS ( branch || account_number )

or

branch AS (substr(full_account_number,1,4)),
account_number AS (substr(full_account_number,5))

and
sha_value AS (sha3(full_account_number,512))

I have one database that has several similar concatenated encodings,
some with 5 parts.
The example given is a simplification, calculated when required would
abbreviate much of my retrieval SQL. (Although views provide a partial
solution)

Niggle 1: I can see why I would like forward references, although not mandatory.
Niggle 2: I have no need for stored computed values



> computed columns - fahrTemp AS (centTemp*9/5 + 32)
> Niggle 1: Can a computed column refer to a column defined after it ?
>Niggle 2: Should a computed value be stored with the other values or calculated when required ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Simon Slavin-3
On 10 Jun 2018, at 1:09am, David Burgess <[hidden email]> wrote:

> full_account_number AS ( branch || account_number )
>
> or
>
> branch AS (substr(full_account_number,1,4)),
> account_number AS (substr(full_account_number,5))

All the above are allowed in computed columns.  There are some functions which are banned.  They are mostly non-deterministic functions.  So a calculation which depends on 'now' would not be allowed.  Similarly for COUNT(*) or the result of a PRAGMA.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

David Burgess-2
> There are some functions which are banned.
Are extension functions permitted?
And how does an extension function author indicate that the function
is deterministic?

On Sun, Jun 10, 2018 at 10:23 AM, Simon Slavin <[hidden email]> wrote:

> On 10 Jun 2018, at 1:09am, David Burgess <[hidden email]> wrote:
>
>> full_account_number AS ( branch || account_number )
>>
>> or
>>
>> branch AS (substr(full_account_number,1,4)),
>> account_number AS (substr(full_account_number,5))
>
> All the above are allowed in computed columns.  There are some functions which are banned.  They are mostly non-deterministic functions.  So a calculation which depends on 'now' would not be allowed.  Similarly for COUNT(*) or the result of a PRAGMA.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Andy Goth-3
In reply to this post by Andy Goth-3
> Notice that the view isn't bound to the numbers table or its x column
> until it's actually used in the SELECT query.  [...]  This might also
> make it possible for a table-valued function to recursively invoke
> itself, provided that it has a basis case to avoid infinite descent.

We already have recursive invocation with common table expressions.
Nevertheless, here's another way to look at it:

CREATE VIEW range AS
      SELECT start
       WHERE CASE WHEN step > 0 THEN start <= stop ELSE start >= stop END
   UNION ALL range(start + step, stop, step)
  PARAMETERS (start, stop, step DEFAULT 1 CHECK (step != 0));

Here I say "range(...)" as shorthand for "SELECT * FROM range(...)", by
analogy with IN and table-valued functions.  With the above, once start
exceeds stop, the engine would have to optimize out the infinite
sequence of UNION ALL against guaranteed-empty results.

It would make sense for table-valued functions to be usable as
expressions, provided that they return exactly one row and one column.

CREATE VIEW double AS SELECT arg * 2 PARAMETERS (arg);
SELECT x, double(x) FROM range(1, 10);

How about table-valued functions as row values, provided they return
exactly one row?

CREATE TABLE foo (a, b, c);
CREATE VIEW multiples AS SELECT x, x * 2, x * 3 PARAMETERS (x);
UPDATE foo SET (a, b, c) = multiples(5) WHERE a = 0;

Table-valued functions are already permitted to be the right-hand of an
IN operator, so these new table-valued functions should be no different,
provided they return exactly one column.

CREATE VIEW multiples AS
      VALUES (x)
   UNION ALL
      VALUES (x * 2)
   UNION ALL
      VALUES (x * 3)
  PARAMETERS (x);
SELECT * FROM foo WHERE (a, b, c) IN multiples(a);

--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Andy Goth-3
In reply to this post by David Burgess-2
On 06/09/18 19:46, David Burgess wrote:
>> There are some functions which are banned.
> Are extension functions permitted?

https://sqlite.org/c3ref/create_function.html

> And how does an extension function author indicate that the function
> is deterministic?

https://sqlite.org/c3ref/c_deterministic.html

--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Andy Goth-3
In reply to this post by Simon Slavin-3
On 06/09/18 18:04, Simon Slavin wrote:
> CREATE TABLE tempLog (
>     datestamp TEXT COLLATE NOCASE PRIMARY KEY,
>     centTemp REAL,
>     fahrTemp AS (centTemp*9/5 + 32) )
>
> I'm happy with another syntax as long as it does the same thing.

CREATE TABLE tempLog (
    datestamp TEXT COLLATE NOCASE PRIMARY KEY
  , centTemp  REAL);
CREATE VIEW tempLogView AS
      SELECT *
           , centTemp * 9 / 5 + 32 AS fahrTemp
        FROM tempLog;

> Niggle 1: Can a computed column refer to a column defined after it ?

With the view syntax I showed above, "computed" columns can only refer
to columns that exist in the underlying tables.  I wish SELECT statement
expressions could refer not only to input columns but also output
columns that have been named using AS, but we don't have this feature.

--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Scott Robison-2
On Sat, Jun 9, 2018 at 7:00 PM, Andy Goth <[hidden email]> wrote:

> On 06/09/18 18:04, Simon Slavin wrote:
>>
>> CREATE TABLE tempLog (
>>     datestamp TEXT COLLATE NOCASE PRIMARY KEY,
>>     centTemp REAL,
>>     fahrTemp AS (centTemp*9/5 + 32) )
>>
>> I'm happy with another syntax as long as it does the same thing.
>
>
> CREATE TABLE tempLog (
>    datestamp TEXT COLLATE NOCASE PRIMARY KEY
>  , centTemp  REAL);
> CREATE VIEW tempLogView AS
>      SELECT *
>           , centTemp * 9 / 5 + 32 AS fahrTemp
>        FROM tempLog;
>
>> Niggle 1: Can a computed column refer to a column defined after it ?
>
>
> With the view syntax I showed above, "computed" columns can only refer
> to columns that exist in the underlying tables.  I wish SELECT statement
> expressions could refer not only to input columns but also output
> columns that have been named using AS, but we don't have this feature.

SELECT *, (computation on Y) AS X FROM (
   SELECT *, (some computation) AS Y FROM sometable)

It is a little annoying having to nest them, but it works.

--
Scott Robison
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Simon Slavin-3
In reply to this post by Andy Goth-3
On 10 Jun 2018, at 2:00am, Andy Goth <[hidden email]> wrote:

> CREATE TABLE tempLog (
>   datestamp TEXT COLLATE NOCASE PRIMARY KEY
> , centTemp  REAL);
> CREATE VIEW tempLogView AS
>     SELECT *
>          , centTemp * 9 / 5 + 32 AS fahrTemp
>       FROM tempLog;

Yes.  That is an elegant and compact way to do it with SQLite as it is now.  But it's not as satisfying.  And it doesn't allow indexing by the computed column.  You have to define the calculation in the index separately.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Andy Goth-3
On 06/09/18 20:10, Simon Slavin wrote:

> On 10 Jun 2018, at 2:00am, Andy Goth <[hidden email]> wrote:
>> CREATE TABLE tempLog (
>>    datestamp TEXT COLLATE NOCASE PRIMARY KEY
>> , centTemp  REAL);
>> CREATE VIEW tempLogView AS
>>      SELECT *
>>           , centTemp * 9 / 5 + 32 AS fahrTemp
>>        FROM tempLog;
>
> Yes.  That is an elegant and compact way to do it with SQLite as it is
> now.  But it's not as satisfying.  And it doesn't allow indexing by
> the computed column.  You have to define the calculation in the index
> separately.

However, views make behavior of INSERT and UPDATE clear, since they can
only operate on the real table.  INSERT or UPDATE become murky when in
the presence of computed columns.  I suppose the only sane thing to do
is forbid directly setting the value of a computed column, though what
would the syntax be?  Skip computed columns in the value list?  If two
tables have the same schema, this should duplicate one into the other,
but apparently not:

INSERT INTO table2 SELECT * from table1;

--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Simon Slavin-3
On 10 Jun 2018, at 2:18am, Andy Goth <[hidden email]> wrote:

> However, views make behavior of INSERT and UPDATE clear, since they can
> only operate on the real table.  INSERT or UPDATE become murky when in
> the presence of computed columns.  I suppose the only sane thing to do
> is forbid directly setting the value of a computed column, though what
> would the syntax be?

One cannot set the value of a computed column in INSERT or UPDATE.  An attempt to do so yields an error in both SQL SERVER and MySQL.  Also, computed columns are skipped in the form of INSERT where the columns are not named.

>   Skip computed columns in the value list?  If two
> tables have the same schema, this should duplicate one into the other,
> but apparently not:
>
> INSERT INTO table2 SELECT * from table1;

This syntax, when found with a computed column, would be something that the SQLite engine would have to notice and act correctly.  The same in the case of CREATE TABLE ... SELECT ... .

There are a number of other niggles.  For instance, creating and updating an index which includes a VIRTUAL calculated column could be complicated and time-consuming.  It may be that for a computed column to appear in an index it must be STORED.  On the other hand, the way SQLite works internally might make that unnecessary.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Keith Medcalf
In reply to this post by Andy Goth-3

create table templog
(
  datestamp text collate nocase primary key,
  centTemp REAL not null,
  fahrTemp REAL
);

create trigger TempLogInsert after insert on templog
begin
 update templog set fahrtemp = new.centTemp/9*5+32 where datestamp == new.datestamp;
end;

create trigger tempLogUpdate after update of centTemp on tempLog
begin
  update templog set fahrtemp = new.centTemp/9*5+32 where datestamp == old.datestamp;
end;

There ya go.  A persistent computed column ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Andy Goth
>Sent: Saturday, 9 June, 2018 19:18
>To: [hidden email]
>Subject: Re: [sqlite] Idea: defining table-valued functions directly
>in SQL
>
>On 06/09/18 20:10, Simon Slavin wrote:
>> On 10 Jun 2018, at 2:00am, Andy Goth <[hidden email]>
>wrote:
>>> CREATE TABLE tempLog (
>>>    datestamp TEXT COLLATE NOCASE PRIMARY KEY
>>> , centTemp  REAL);
>>> CREATE VIEW tempLogView AS
>>>      SELECT *
>>>           , centTemp * 9 / 5 + 32 AS fahrTemp
>>>        FROM tempLog;
>>
>> Yes.  That is an elegant and compact way to do it with SQLite as it
>is
>> now.  But it's not as satisfying.  And it doesn't allow indexing by
>> the computed column.  You have to define the calculation in the
>index
>> separately.
>
>However, views make behavior of INSERT and UPDATE clear, since they
>can
>only operate on the real table.  INSERT or UPDATE become murky when
>in
>the presence of computed columns.  I suppose the only sane thing to
>do
>is forbid directly setting the value of a computed column, though
>what
>would the syntax be?  Skip computed columns in the value list?  If
>two
>tables have the same schema, this should duplicate one into the
>other,
>but apparently not:
>
>INSERT INTO table2 SELECT * from table1;
>
>--
>Andy Goth | <andrew.m.goth/at/gmail/dot/com>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Andy Goth-3
In reply to this post by Simon Slavin-3
On 6/9/2018 8:31 PM, Simon Slavin wrote:

> On 10 Jun 2018, at 2:18am, Andy Goth <[hidden email]> wrote:
>> Skip computed columns in the value list?  If two tables have the same
>> schema, this should duplicate one into the other, but apparently not:
>>
>> INSERT INTO table2 SELECT * from table1;
>
> This syntax, when found with a computed column, would be something
> that the SQLite engine would have to notice and act correctly.  The
> same in the case of CREATE TABLE ... SELECT ... .
>
> There are a number of other niggles.  For instance, creating and
> updating an index which includes a VIRTUAL calculated column could be
> complicated and time-consuming.  It may be that for a computed column
> to appear in an index it must be STORED.  On the other hand, the way
> SQLite works internally might make that unnecessary.
I think it's clear why SQLite doesn't already have this feature.  The
required underlying capability is already available.  The remaining bits
are a convenience to the end user at the cost of significant added
complexity inside the engine.  This is probably not going to be added
until SQLite's primary developers themselves decide they need it, or
until someone else decides it's important enough to them to contribute
code and/or funding.

SQLite doesn't seek to implement all things SQL, just the parts that
have proven to be a workhorse.  There's been a gradual shift as more
development hours have been invested, but the "Lite" still means
something.

https://sqlite.org/omitted.html

I've watched this list get shorter over the years, but the items that
remain are increasingly likely to become permanent fixtures.

And hey, that list was a welcome reminder that views can have triggers
which can translate back to real DELETE, INSERT, and UPDATE operations
on the underlying table.  This might be what you want.

SQLite's embeddability and ease of integration (particularly with Tcl)
makes it very easy to wrap, so you can give yourself commands that
generate and access tables using whatever syntax works best for you.
You can write code that intercepts and translates the computed column
syntax of your choice to automatically generated fancy views with
triggers.  Once you get the semantics nailed down and others find your
work useful, that effort can serve as a reference implementation for
actually adding the capability to SQLite itself.

--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

David Burgess-2
> reminder that views can have triggers

Anyone else have an issue with trigger performance in SQLite?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Keith Medcalf

Do you have an appropriate index defined on both the parent and child keys?

.lint fkey-indexes

at a command shell prompt will tell you ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of David Burgess
>Sent: Saturday, 9 June, 2018 21:44
>To: SQLite mailing list
>Subject: Re: [sqlite] Idea: defining table-valued functions directly
>in SQL
>
>> reminder that views can have triggers
>
>Anyone else have an issue with trigger performance in SQLite?
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

Keith Medcalf

Skip that, that is foreign-keys.  What problem are you having with trigger performance?


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Keith Medcalf
>Sent: Saturday, 9 June, 2018 22:06
>To: SQLite mailing list
>Subject: Re: [sqlite] Idea: defining table-valued functions directly
>in SQL
>
>
>Do you have an appropriate index defined on both the parent and child
>keys?
>
>.lint fkey-indexes
>
>at a command shell prompt will tell you ...
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of David Burgess
>>Sent: Saturday, 9 June, 2018 21:44
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Idea: defining table-valued functions directly
>>in SQL
>>
>>> reminder that views can have triggers
>>
>>Anyone else have an issue with trigger performance in SQLite?
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

David Burgess-2
> What problem are you having with trigger performance?

My primary use of triggers in SQLite is on insert/update and using RAISE.

Not sure where the trigger overhead comes from, but
If I place a simple check in CHECK and have a trigger with the same CHECK then
CHECK is way faster than a trigger.

(on 3.18)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

sqlite-10
In reply to this post by Andy Goth-3
I have some of my own ideas about this.

* Perhaps move PARAMETERS before AS, which may make the syntax easier.

* You don't need computed columns in tables; use views instead. You can index computed values though.

* 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.

* Another (separate) idea can be "CREATE FUNCTION name(args) AS select_stmt;" to define your own function. If you write "CREATE AGGREGATE FUNCTION" then the function name can be used as a table name within the select_stmt. Both of these are separate from table-valued functions (parameterized views) though.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Idea: defining table-valued functions directly in SQL

J Decker
In reply to this post by Andy Goth-3
On Sat, Jun 9, 2018 at 7:37 PM Andy Goth <[hidden email]> wrote:

>
> This is probably not going to be added
> until SQLite's primary developers themselves decide they need it, or
> until someone else decides it's important enough to them to contribute
> code and/or funding.
>

forget contributing code ... but funding?  how much can I pay to get better
NUL support?  and get the table alias name for a column from a prepared
statement?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12