WATCH Statement (Experimental)
This is an experimental feature that may change in backwards-incompatible ways in the future releases. Enable live views and WATCH query using set allow_experimental_live_view = 1.
WATCH [db.]live_view
[EVENTS]
[LIMIT n]
[FORMAT format]
The WATCH query performs continuous data retrieval from a LIVE VIEW table. Unless the LIMIT clause is specified it provides an infinite stream of query results from a LIVE VIEW.
WATCH [db.]live_view [EVENTS] [LIMIT n] [FORMAT format]
Virtual columns
The virtual _version column in the query result indicates the current result version.
Example:
CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();
WATCH lv;
┌───────────────now()─┬─_version─┐
│ 2021-02-21 09:17:21 │ 1 │
└─────────────────────┴──────────┘
┌───────────────now()─┬─_version─┐
│ 2021-02-21 09:17:26 │ 2 │
└─────────────────────┴──────────┘
┌───────────────now()─┬─_version─┐
│ 2021-02-21 09:17:31 │ 3 │
└─────────────────────┴──────────┘
...
By default, the requested data is returned to the client, while in conjunction with INSERT INTO it can be forwarded to a different table.
Example:
INSERT INTO [db.]table WATCH [db.]live_view ...
EVENTS Clause
The EVENTS clause can be used to obtain a short form of the WATCH query where instead of the query result you will just get the latest query result version.
WATCH [db.]live_view EVENTS;
Example:
CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();
WATCH lv EVENTS;
┌─version─┐
│ 1 │
└─────────┘
┌─version─┐
│ 2 │
└─────────┘
...
LIMIT Clause
The LIMIT n clause specifies the number of updates the WATCH query should wait for before terminating. By default there is no limit on the number of updates and therefore the query will not terminate. The value of 0 indicates that the WATCH query should not wait for any new query results and therefore will return immediately once query result is evaluated.
WATCH [db.]live_view LIMIT 1;
Example:
CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();
WATCH lv EVENTS LIMIT 1;
┌─version─┐
│ 1 │
└─────────┘
FORMAT Clause
The FORMAT clause works the same way as for the SELECT.
The JSONEachRowWithProgress format should be used when watching LIVE VIEW tables over the HTTP interface. The progress messages will be added to the output to keep the long-lived HTTP connection alive until the query result changes. The interval between progress messages is controlled using the live_view_heartbeat_interval setting.