TSCreateVirtualTab procedure

The TSCreateVirtualTab procedure creates a virtual table that is based on a table that contains a TimeSeries column.

Syntax

TSCreateVirtualTab(VirtualTableName  LVARCHAR,
                   BaseTableName     LVARCHAR,
                   NewTimeSeries     LVARCHAR,
                   TSVTMode          INTEGER default 0,
                   TSColName         LVARCHAR default NULL);

TSCreateVirtualTab(VirtualTableName  LVARCHAR,
                   BaseTableName     LVARCHAR,
                   NewTimeSeries     LVARCHAR,
                   TSVTMode          LVARCHAR,
                   TSColName         LVARCHAR default NULL);

TSCreateVirtualTab(VirtualTableName  lvarchar,
                   BaseTableName     LVARCHAR,
                   NewTimeSeries     LVARCHAR,
                   TSVTMode          INTEGER default 0,
                   TSColName         LVARCHAR default NULL);

VirtualTableName: The name of the new virtual table.
BaseTableName: The name of the base table.
NewTimeSeries (optional): Controls whether you can insert elements into a time series that does not yet exist in the base table either because the row does not exist or because the row does not yet have a time series instance.; A time series input sting = The definition of the new time series to create. For syntax, see Time series input string.; NULL = Default. New time series creation is disabled.
TSVTMode (optional): Sets the virtual table mode, as described in The TSVTMode parameter. Can be an integer or a string of one or more flag names that are separated by one of the following delimiters: plus sign (+), pipe (|), or comma (,).
TSColName (optional): For base tables that have more than one TimeSeries column, specifies the name of the TimeSeries column to be used to create the virtual table. The default value for the TSColName parameter is NULL, in which case the base table must have only one TimeSeries column.

Usage

Use the TSCreateVirtualTab procedure to create a virtual table that is based on a table that contains a time series. Because the column names in the TimeSeries row type are used as the column names in the resulting virtual table, you must ensure that these column names do not conflict with the names of other columns in the base table. The total length of a row in the virtual table (non-time-series and TimeSeries columns combined) must not exceed 32 KB.

You can configure the time series virtual table to allow updating data in the base table through the virtual table. If you specify any of the optional parameters, you must include them in the order that is shown in the syntax, but you can use any one of them without using the others. For example, you can specify the TSColName parameter without including the NewTimeSeries and the TSVTMode parameters.

The NewTimeSeries parameter

The NewTimeSeries parameter specifies whether the virtual table allows elements to be inserted into a time series that does not yet exist in the base table either because the row does not exist or because the row does not yet have a time series instance. To allow inserts if a time series does not yet exist, use the NewTimeSeries parameter to specify the time series input string. To prohibit inserts if a time series does not yet exist, omit the NewTimeSeries parameter when you create the virtual table.

The following table describes the results of attempting to update the base table for different goals.

Table 1. Behavior of updates to the base table
Goal	Result	Need to use the NewTimeSeries parameter?
Add a time series element into an existing row that does not have any time series data. For example, add the first meter reading for a specific meter.	A new time series is inserted in the existing row.	Yes
Add a time series element to an existing time series. For example, add a meter reading for a meter that has previous readings.	If the timepoint is not the same as an existing element, the new element is inserted to the time series. If the timepoint is the same as an existing element, the existing element is updated with the new value. If the `TSVTMode` parameter includes the value 1 or putelem, multiple elements for the same timepoint can coexist, therefore the new element is inserted, and the existing element is also retained. If the `TSVTMode` parameter includes the value 1, multiple elements for the same timepoint can coexist, therefore the new element is inserted, and the existing element is also retained.	No
Add a row. For example, add a row for a new meter ID.	A new row is inserted into the base table.	Yes

If you do not include the NewTimeSeries parameter and attempt to insert a time series element into an existing row that does not have any time series elements or into a new row, you receive an error.

Example

The following example creates a virtual table that is called daily_stocks_virt based on the table daily_stocks. Because this example specifies a value for the NewTimeSeries parameter, the virtual table daily_stocks_virt allows inserts if a time series does not exist for an element in the underlying base table. If you perform such an insert, the database server creates a new empty time series that uses the calendar daycal and has an origin of January 3, 2011.

EXECUTE PROCEDURE TSCreateVirtualTab('daily_stocks_virt', 
         'daily_stocks', 'calendar(daycal), 
         origin(2011-01-03 00:00:00.00000)' );

The following statement creates a virtual table with the same characteristics as the previous statement, except that the TSVTMode parameter specifies to allow duplicate timepoints and to reduce logging:

EXECUTE PROCEDURE TSCreateVirtualTab('daily_stocks_virt', 
         'daily_stocks', 'calendar(daycal), 
         origin(2011-01-03 00:00:00.00000)',
         'put_elem+reduced_log' );