[
https://issues.apache.org/jira/browse/DERBY-6467?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14001886#comment-14001886
]
Rick Hillegas commented on DERBY-6467:
--------------------------------------
Thanks for the patch, Kim. I can see that users may be confused by my original
explanation of the feature. The following explanation makes more sense, I think:
"Context-aware table functions are useful when you want to do the following:
o Bind a single Java method to many table functions, each of which has a
different row shape...
o ...and you are able to determine the row shape, at runtime, from the
schema-qualified name of the table function which is being invoked."
Then I think that the example could be improved by declaring a second table
which has a different shape and show that the
org.apache.derbyTesting.functionTests.tests.lang.ArchiveVTI.archiveVTI can be
used to handle archives of the second table too. So something like this:
{noformat}
connect 'jdbc:derby:memory:db;create=true';
create table t1
(
keyCol int,
aCol int,
bCol int
);
create table t1_archive_001 as select * from t1 with no data;
create table t1_archive_002 as select * from t1 with no data;
insert into t1_archive_002 values ( 1, 100, 1000 ), ( 2, 200, 2000 ), ( 3,
300, 3000 );
insert into t1_archive_001 values ( 4, 400, 4000 ), ( 5, 500, 5000 ), ( 6,
600, 6000 );
insert into t1 values ( 7, 700, 7000 ), ( 8, 800, 8000 ), ( 9, 900, 9000 );
create table t2
(
keyCol int,
aCol int
);
create table t2_arc_001 as select * from t2 with no data;
create table t2_arc_002 as select * from t2 with no data;
insert into t2_arc_002 values ( 1, 100 ), ( 2, 200 ), ( 3, 300 );
insert into t2_arc_001 values ( 4, 400 ), ( 5, 500 ), ( 6, 600 );
insert into t2 values ( 7, 700 ), ( 8, 800 ), ( 9, 900 );
create function t1( archiveSuffix varchar( 32672 ) ) returns table
(
keyCol int,
aCol int,
bCol int
)
language java parameter style derby_jdbc_result_set reads sql data
external name
'org.apache.derbyTesting.functionTests.tests.lang.ArchiveVTI.archiveVTI';
create function t2( archiveSuffix varchar( 32672 ) ) returns table
(
keyCol int,
aCol int
)
language java parameter style derby_jdbc_result_set reads sql data
external name
'org.apache.derbyTesting.functionTests.tests.lang.ArchiveVTI.archiveVTI';
select * from table( t1( '_ARCHIVE_' ) ) s
where keyCol between 3 and 7
order by keyCol;
select * from table( t2( '_ARC_' ) ) s
where keyCol between 3 and 7
order by keyCol;
{noformat}
I would also include the source code for ArchiveVTI. That will help users
understand how to use the AwareVTI interface.
Thanks,
-Rick
> Document context-aware table functions.
> ---------------------------------------
>
> Key: DERBY-6467
> URL: https://issues.apache.org/jira/browse/DERBY-6467
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.11.0.0
> Reporter: Rick Hillegas
> Assignee: Kim Haase
> Attachments: DERBY-6467.diff, DERBY-6467.stat,
> cdevspecialtfcontext.html
>
>
> Now that we have added AwareVTI and VTIContext to the public api, it would be
> good to document how to use them. Perhaps we could add a "Writing a
> context-aware table function" subtopic under the "Programming Derby-style
> table functions" topic in the Developer's Guide.
> As an example, the new subsection could use ArchiveVTI together with the
> script which is part of the 2014-02-02 comment on DERBY-6117.
> Context-aware table functions are useful when you need to describe a family
> of results which have the following properties:
> 1) Each result in the family has a shape which is determined by a different
> table.
> 2) There may be several results associated with a given table. In addition to
> having the same shape, the sub-family of results differ from one another
> based on some variables which cannot be represented as ? parameters in a
> SELECT statement.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
