TSL_PutSQL function

The TSL_PutSQL function loads time series data from a table.

Syntax

TSL_PutSQL(
           handle      lvarchar,
           statement   lvarchar)
returns integer

handle

The table and column name combination that is returned by the TSL_Attach or the TSL_Init function.

statement

An SQL statement that selects data from an existing database table. The projection clause of the statement must consist of a primary key and the time series data. The time series data can be multiple columns or a ROW data type and must be compatible with the data type of the TimeSeries column in the handle. The projection clause can be one of the following formats:

primary_key, timestamp, values

primary_key, row(timestamp, values)

primary_key: The primary key column, represented in character format. Can consist of multiple column names concatenated and separated by a pipe symbol. Cast to an LVARCHAR data type, if necessary. For example, if the primary key in the source table consists of two columns, named id1 and id2, the primary key column in the projection clause is id1 || '|' || id2.
timestamp: The format of the timestamp is specified by the timestamp_format argument of the TSL_Init function. The default format of the timestamp is: YYYY-mm-dd HH:MM:SS (year-month-day hour:minute:seconds).
values: Values for the columns in the TimeSeries subtype. Separate multiple values with commas.

Usage

Use the TSL_PutSQL function to load time series data from another table as part of a loader program. You must run the TSL_PutSQL function in the context of a loader session that was initialized by the TSL_Init function.

You can run the TSL_PutSQL function multiple times in the same session. The data is stored in the database server until you run the TSL_Flush function to write the data to disk.

Returns

An integer that indicates the number of records that were inserted.
An exception if no records were inserted.

Examples

These examples run in the context of an initialized loader session.

Example 1: Load a primary key and multiple columns

The following example selects data from a table named dataload from a primary key column, a timestamp column, and a column with other values:

EXECUTE FUNCTION TSL_PutSQL('meter|readings', 
	'SELECT id::lvarchar, tstamp, value FROM dataload');

Example 2: Load a primary key and a ROW data type

The following example selects data from a table named dataload from a primary key column and a ROW data type that consists of a timestamp field and a value field:

EXECUTE FUNCTION TSL_PutSQL('meter|readings', 
	'SELECT id::lvarchar, row(tstamp, value) FROM dataload');