Preview

This feature is in preview and is subject to change.

RESULT

The RESULT statements let you run a SELECT asynchronously, cache rows as they are produced, and query that cached output like a table.

This is most valuable for presenting users in a data application with rows as they come in instead of waiting for the full result. Use the Results concept page for lifecycle behavior and caching details.

Syntax

CREATE [ OR REPLACE ] RESULT [ IF NOT EXISTS ] <result_name> AS <select_query>;

FROM <result_name> [ LIMIT <n> ];

PAUSE RESULT [ IF EXISTS ] <result_name>;
RESUME RESULT [ IF EXISTS ] <result_name>;
CANCEL RESULT [ IF EXISTS ] <result_name>;
DROP RESULT [ IF EXISTS ] <result_name>;

SHOW ALL RESULTS;
FROM md_show_results();

RESULT accepts SELECT statements only. The result starts building asynchronously when it is created.

OR REPLACE clause

OR REPLACE drops the existing result and creates a new one with the same name.

IF NOT EXISTS clause

If a result with that name already exists, IF NOT EXISTS suppresses the error and leaves the existing result unchanged.

Accessing a result

You can query a result like a table:

FROM <result_name>;
FROM <result_name> LIMIT <n>;

The cached output may be partial while the result is still building. Depending on the query and the current state of the result, MotherDuck may read from the cache, wait for more rows, or re-run the underlying SELECT.

Lifecycle statements

You can manage a result after creation with:

• PAUSE RESULT
• RESUME RESULT
• CANCEL RESULT
• DROP RESULT

CANCEL RESULT permanently stops the background job. A canceled result cannot be resumed.

Introspecting results

Use SHOW ALL RESULTS to inspect all results in the current session.

If you want to filter, sort, or limit that output, use md_show_results().

Output

SHOW ALL RESULTS and md_show_results() return:

Column Name	Data Type	Value
name	VARCHAR	Name of the result
error	VARCHAR	Error message, if any
status	VARCHAR	Current status: BUILDING, PAUSED, or DONE
row_count	BIGINT	Number of cached rows available

Example usage

CREATE RESULT top_urls AS
SELECT url, count(*) AS requests
FROM pypi_downloads
GROUP BY url;

FROM top_urls LIMIT 10;

SHOW ALL RESULTS;

FROM md_show_results()
WHERE name = 'top_urls';

Notes

• Results are stored in memory and are not persisted across client restarts.
• DROP RESULT permanently removes the result and its cached rows.