TRUNCATE Statement

Syntax 

TRUNCATE table_identifier USING {stream_identifier | stream_expression}
  [RETURNING target_list]
  [ERROR INTO stream_identifier];

Substitutable Fields

table_identifier

The unique identifier (name) of the table to truncate.

stream_identifier

The unique identifier (name) of the stream that, when a tuple is present, will cause truncation of the table.

stream_expression

A StreamSQL statement that produces a stream. The statement must be enclosed within parentheses.

target_list

One or more entries, separated by commas, of the format target_list_entry.

target_list_entry

A value, of the format scalar_expression [AS output_field_identifier], to be included in the result set returned by the statement.

scalar_expression

An expression that generates a value for the tuple that is returned by the truncate operation. Values can be obtained from a field in the input tuple or from a simple function. Optionally, the name for a value can be modified through an AS clause.

ERROR INTO Clause

You can append an ERROR INTO clause just before the closing semicolon. The StreamSQL ERROR INTO clause is analogous to the Enable Error Output Port check box for operators and adapters in EventFlow applications.

Use ERROR INTO with the name of a stream, which must already exist. This sets up an Error Port for this operator, which is much like a local catch mechanism for errors from this operator.

See Using Error Ports and Error Streams for a discussion of StreamBase error handling mechanisms.

Discussion

The TRUNCATE statement completely empties, but does not delete, a table.

With the TRUNCATE statement, the USING clause identifies the associated stream. The actual content of the tuples contained within this stream are not used in executing the stream query or query statement. Just the presence of a tuple on this stream causes the query or statement to execute, which deletes the entire contents of the table but leaves the table itself.

In the RETURNING clause, scalar_expression can specify a value from a StreamBase function, any field from the tuple on the input stream, and/or from the table. However, since the contents of the table have been deleted, any value derived from the table will be null. Therefore, it only makes sense to include function or input tuple field values in the RETURNING clause. For example: 

TRUNCATE ...
  RETURNING scalar_expression [AS ...][, ...]

The result set generated by the RETURNING clause must be captured into a stream. You can use the CREATE STREAM Statement statement to define a stream and the INTO keyword to populate the stream with the content generated by the RETURNING clause. As an alternative, in a single statement use the => (arrow) operator with a CREATE STREAM statement, as illustrated below. 

CREATE STREAM stream_identifier;
TRUNCATE ... RETURNING ... INTO stream_identifier;

Or 

TRUNCATE ... RETURNING ... => CREATE STREAM stream_identifier

Related Topics

Back to Top ^