OnchainQL contract

Every request must contain exactly one query beginning with SELECT or WITH. A trailing semicolon is optional. OnchainQueries does not accept multiple statements, data modification, schema changes or session configuration.

Supported surface

• projections, expressions and aliases
• WHERE, CASE, IN, BETWEEN and ordinary predicates
• DISTINCT, GROUP BY, aggregates and HAVING
• ORDER BY with ASC/DESC and NULLS FIRST/NULLS LAST
• LIMIT and OFFSET
• ordinary CTEs and subqueries
• common inner, left, right, full and cross joins
• common window functions and frames
• UNION, INTERSECT and EXCEPT where the execution layer accepts the normalized form

Every query must bound the time range it reads. Any SELECT that reads a source table needs a WHERE clause putting a lower bound on ts, so no query can scan all of history by omission.

SELECT count(*)FROM pump_fun.trade_eventWHERE ts >= TIMESTAMP '2026-08-01 00:00:00';

An upper bound is not required — ts >= now() - INTERVAL '7' DAY is a complete bound. Any of these establish one:

The bound must sit on the SELECT that reads the table, not on an enclosing query. In a CTE, subquery or derived table, put it inside:

WITH hourly AS (  SELECT date_trunc('hour', ts) AS hour, count(*) AS trades  FROM pump_fun.trade_event  WHERE ts >= {{start_time}}    -- here, not on the outer SELECT  GROUP BY 1)SELECT * FROM hourly ORDER BY hour;

Each arm of a UNION, INTERSECT or EXCEPT is checked separately. A bound that only holds on one side of an OR does not count, because the other side is still unbounded. In a join, bound each side you want restricted.

None of the following are allowed:

• INSERT, UPDATE, DELETE and other data modification
• CREATE, ALTER, DROP and other schema modification
• SET, USE, SYSTEM, grants and administrative statements
• user-controlled FORMAT, SETTINGS or INTO OUTFILE
• access to the execution layer’s internal tables
• external table functions such as s3, url, file, postgresql, mysql, remote and iceberg

VALUES relations and saved-query views such as query_123 are not part of the supported catalog. Submit requests, including SQL and parameter JSON, are limited to 2 MiB.

SELECT  mint,  count(*) AS trades,  sum(sol_amount) AS sol_volumeFROM pump_fun.trade_eventWHERE ts >= {{start_time}}  AND ts < {{end_time}}  AND is_buy = trueGROUP BY mintHAVING count(*) >= 10ORDER BY sol_volume DESC, mint ASCLIMIT 100;

Multiple sort expressions, positional references such as ORDER BY 2 DESC, explicit null placement and LIMIT/OFFSET pagination all work. Rows tied on every ordering expression have no guaranteed relative order — add a unique tie-breaker when stable pagination matters.

WITH hourly AS (  SELECT    date_trunc('hour', ts) AS hour,    sum(sol_amount) AS volume  FROM pump_fun.trade_event  WHERE ts >= {{start_time}}  GROUP BY hour)SELECT  hour,  volume,  sum(volume) OVER (    ORDER BY hour    ROWS BETWEEN 23 PRECEDING AND CURRENT ROW  ) AS rolling_24h_volumeFROM hourlyORDER BY hour;

Use common JOIN ... ON or JOIN ... USING forms. Joins execute as one query rather than partitioned export tasks, so add selective time and key predicates to both sides.

Array literals use bracket syntax: SELECT ARRAY[1, 2, 3]. The supported UNNEST form is a cross join over one column identifier with a table and column alias, which OnchainQueries rewrites to a an array join.

SELECT  t.slot,  path_indexFROM pump_fun.trade_event AS tCROSS JOIN UNNEST(t.ix_path) AS p(path_index)WHERE t.ts >= {{start_time}};

OnchainQueries evaluates TRY_CAST through a local evaluation step so invalid values become NULL.

SELECT TRY_CAST(mint AS BIGINT) AS mint_numberFROM pump_fun.trade_eventWHERE slot > 0;

A simple single-table projection can be divided into independent daily tasks when it has no CTE, join, distinct, grouping, ordering, limit, offset, window or set operation, and includes ts in the output. This is the most efficient shape for large exports.

SELECT  ts,  slot,  sig,  mint,  sol_amountFROM pump_fun.trade_eventWHERE ts >= {{start_time}}  AND ts < {{end_time}};

• constrain ts whenever possible
• select only required columns
• aggregate before sorting large result sets
• use LIMIT for exploratory top-N queries
• omit a global ORDER BY when downstream tools can sort the exported parts
• prefer --engine auto first, then select a larger class only when necessary

Port incrementally: verify the table coverage, replace quoted parameters, start with the core projection and filters, then add functions, joins, windows and arrays one at a time. A query succeeding because a function with the same name happens to exist underneath does not guarantee OnchainQL argument, null, type-coercion or return-type semantics.