Categories:

System functions (System Control)

SYSTEM$SET_ROW_TIMESTAMP_ON_ALL_SUPPORTED_TABLES

使用此系统函数批量启用现有表上的行时间戳。

此函数将向容器内所有符合条件的现有表添加行时间戳列，并确保新创建的表自动启用行时间戳。

要成功执行该函数，您需要对所调用的容器拥有 MODIFY 权限。

After row timestamps are enabled, tables expose the METADATA$ROW_LAST_COMMIT_TIME column, which returns the timestamp when each row was last modified. This enables change tracking, incremental processing, and time-travel queries based on row modification time. For more information, see Use row timestamps to measure latency in your pipelines.

语法

SELECT SYSTEM$SET_ROW_TIMESTAMP_ON_ALL_SUPPORTED_TABLES('<level>', '<qualified_name>')


- The first argument is level: one of :code:`schema`, :code:`database`, or :code:`account`.
- The second argument is the fully qualified name of the container.

实参

必填

'level'

Container level. Can be one of the following: account, database, schema.

'qualified_name'

The fully qualified name of the container. For example, my_db.myschema for schema level.

示例

以下示例演示了如何使用系统函数为特定模式内所有支持的表批量启用行时间戳。它还会验证该功能已应用于现有表，并设置架构级默认值，以确保所有未来创建的表都会自动包含 METADATA$ROW_LAST_COMMIT_TIME 列。

CREATE OR REPLACE DATABASE my_db;
CREATE OR REPLACE SCHEMA my_schema;
USE DATABASE my_db;
USE SCHEMA my_schema;

CREATE OR REPLACE TABLE my_table (id INT, v STRING);
CREATE OR REPLACE TRANSIENT TABLE my_transient_table (id INT, v STRING);
CREATE OR REPLACE TEMP TABLE my_temp_table (id INT, v STRING);

SELECT SYSTEM$SET_ROW_TIMESTAMP_ON_ALL_SUPPORTED_TABLES(
  'schema',
  'my_db.my_schema'
);

-- System function sets the container default so that new tables will get row timestamp going forward
SHOW PARAMETERS LIKE 'ROW_TIMESTAMP_DEFAULT' IN SCHEMA my_db.my_schema;

INSERT INTO my_table VALUES (1, 'a'), (2, 'b');
INSERT INTO my_transient_table VALUES (10, 'x');
INSERT INTO my_temp_table VALUES (100, 'tmp');

SELECT ID, METADATA$ROW_LAST_COMMIT_TIME FROM my_table ORDER BY ID;

SELECT ID, METADATA$ROW_LAST_COMMIT_TIME FROM my_transient_table ORDER BY ID;

SELECT ID, METADATA$ROW_LAST_COMMIT_TIME FROM my_temp_table ORDER BY ID;