[
https://issues.apache.org/jira/browse/DERBY-4357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12750000#action_12750000
]
Chris Goodacre commented on DERBY-4357:
---------------------------------------
Regarding the functional spec, its emphasis is on performance tuning, which
follows our original discussion thread. Is it worth discussing other
information that might be informative to the TableFunction implementation? For
instance: say I set out to use Derby as a relational cache for a separate
system. I write a TableFunction that checks the freshness of the data in a
table (inside a Derby database) that mirrors the external system. I'll call
that local Derby table the "cache table". If the data is stale, I grab the data
from the external system and push it into the cache table.
Question: Is there a way to write this function generically? (I don't think so,
since the TableFunction doesn't know which table the query was executed
against). If I have each customer in their own database or schema, how do I
know which database or schema I should check the cache table in?
Admittedly, these are not TableFunction performance optimizations. Should they
be a separate enhancement request or do we deal with all
tablefunction-informing APIs at once?
> TableFunctions provide no information to limit underlying query
> ---------------------------------------------------------------
>
> Key: DERBY-4357
> URL: https://issues.apache.org/jira/browse/DERBY-4357
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0
> Environment: ALL
> Reporter: Chris Goodacre
> Attachments: RestrictedTableFunctions.html
>
>
> The API specification for TableFunctions cannot provide information to the
> implementer of the TableFunction about the details of the query. For
> example:
> (a) I defined a table function named MyFunction with columns a,b, & c
> (b) I bind the table function properly using the CREATE FUNCTION SQL.
> User executes the following SQL:
> select a,b from table ( MyFunction() ) where c = 123
> Without passing the column list and/or where clause as arguments to the table
> function, my implementation can not know that it only needs two of the three
> columns, and only rows where c = 123.
> For TableFunctions that are built to integrate distant/legacy data, the cost
> of the query can be prohibitive. It would be better if information
> regarding the columns in the select and restrictions from the where clause
> could be passed to the developer.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
