read_contract

Calls a contract function against live, pinned, or retained execution state and returns ABI-decoded SQL values. Without a CLIENT, it builds a reusable READ and does not execute the call.

Example

Needs RPC · RPC required

1
-- Table-driven ERC-20 balances, exact raw UINT256 plus display strings
2
WITH tokens(symbol, token_address, holder, decimals) AS (
3
VALUES
4
('WETH', '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS,
5
'0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS, 18),
6
('USDC', '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
7
'0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS, 6)
8
),
9
balances AS (
10
SELECT
11
symbol,
12
decimals,
13
read_contract(
14
$client,
15
token_address,
16
'[{
17
"type": "function",
18
"name": "balanceOf",
19
"stateMutability": "view",
20
"inputs": [{ "name": "account", "type": "address" }],
21
"outputs": [{ "name": "balance", "type": "uint256" }]
22
}]'::JSON,
23
'balanceOf',
24
'{"block_tag":"finalized"}'::JSON,
25
holder
26
) AS raw_balance
27
FROM tokens
28
)
29
SELECT
30
symbol,
31
raw_balance,
32
CASE symbol
33
WHEN 'USDC' THEN format_units(raw_balance, 6)
34
ELSE format_units(raw_balance, 18)
35
END AS balance
36
FROM balances;
Notebook ready in readonly mode.

API reference

Exact signatures with descriptions, requirements, inputs, returns, and examples.

read_contract(CLIENT, ADDRESS, JSON, VARCHAR, ...args) # Immediate Read · RPC required

Calls a contract function and returns typed SQL output derived from a literal or foldable ABI plus a constant function name. Use the options + return_schema overload when the ABI comes from a CTE, materialized CTE, table, or other dynamic expression.

Inputs

Name Type Use
client CLIENT

Readable EVM state represented by a live, pinned, or execution CLIENT.

 required positional
to ADDRESS

Contract address to call.

 required positional
abi JSON

Literal or foldable JSON ABI used for argument encoding and return typing.

 required positional
function_name VARCHAR

Constant ABI function name.

 required positional
...args dynamic ANY

Pass one SQL value per ABI input. The ABI determines how many values are accepted and how each value is cast. The ABI and function name must be literal or foldable so the return type can be inferred during planning.

 positional

Returns

Name Type
result dynamic ABI-derived scalar or STRUCT

A function with one output returns that value directly. A function with multiple outputs returns a STRUCT with fields named from ABI outputs, or output0, output1, and so on for unnamed outputs.

Guidance

Contract read workflow fit

A literal or foldable ABI lets the binder infer the exact SQL return type from Solidity outputs.

When the ABI comes from a CTE, materialized CTE, table, or other row value, use the options + return_schema overload.

The default block context is latest. For reproducible historical reads, pass options with block_number or blockNumber.

  • block_number/blockNumber takes precedence over block_tag.
  • on_error/onError accepts raise or null.
  • rpc_batch_size must be an integer greater than or equal to 1.
  • Use raw_call/call_decode for raw single-call diagnostics.
  • Use encode_function_data, read_contract_multicall, and call_decode for table-shaped batched reads.

Named parameters · RPC required

1
WITH tokens(symbol, token_address, holder, decimals) AS (
2
VALUES
3
('WETH', '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS,
4
'0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS, 18)
5
),
6
raw_balances AS (
7
SELECT
8
symbol,
9
decimals,
10
read_contract(
11
$client,
12
token_address,
13
'[{"type":"function","name":"balanceOf","inputs":[{"name":"account","type":"address"}],"outputs":[{"name":"balance","type":"uint256"}]}]'::JSON,
14
'balanceOf',
15
'{"block_number":21000000}'::JSON,
16
holder
17
) AS raw_balance
18
FROM tokens
19
)
20
SELECT symbol, raw_balance, format_units(raw_balance, decimals) AS balance
21
FROM raw_balances
22
ORDER BY sort_key(raw_balance) DESC;
Notebook ready in readonly mode.

Pinned account snapshots

For account snapshots, pin every read to the same block_number and keep raw UINT256 values next to formatted display values.

Code hashes are bytecode evidence for the token contract at the same block; compute them with code_at plus keccak256.

  • Read decimals, balanceOf, allowance, and code hash in one table-shaped workflow.
  • Use format_units only after the raw balance or allowance is selected.
  • Archive-capable RPC may be required for older block heights.

Named parameters · RPC required

1
-- Pinned ERC-20/account snapshot at Ethereum block 21000000
2
WITH params AS (
3
SELECT
4
'0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS AS account,
5
'0x111111125421cA6dc452d289314280a0f8842A65'::ADDRESS AS spender,
6
21000000::BIGINT AS block_number
7
),
8
tokens(symbol, token_address, decimals) AS (
9
VALUES
10
('WETH', '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS, 18),
11
('USDC', '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS, 6)
12
),
13
snapshot AS (
14
SELECT
15
t.symbol,
16
t.token_address,
17
keccak256(code_at($client, t.token_address, p.block_number)) AS code_hash,
18
read_contract_at(
19
$client, t.token_address,
20
'[{"type":"function","name":"decimals","inputs":[],"outputs":[{"name":"decimals","type":"uint8"}]}]'::JSON,
21
'decimals', p.block_number
22
) AS onchain_decimals,
23
read_contract_at(
24
$client, t.token_address,
25
'[{"type":"function","name":"balanceOf","inputs":[{"name":"account","type":"address"}],"outputs":[{"name":"balance","type":"uint256"}]}]'::JSON,
26
'balanceOf', p.block_number, p.account
27
) AS raw_balance,
28
read_contract_at(
29
$client, t.token_address,
30
'[{"type":"function","name":"allowance","inputs":[{"name":"owner","type":"address"},{"name":"spender","type":"address"}],"outputs":[{"name":"allowance","type":"uint256"}]}]'::JSON,
31
'allowance', p.block_number, p.account, p.spender
32
) AS raw_allowance,
33
t.decimals
34
FROM tokens t, params p
35
)
36
SELECT
37
symbol,
38
token_address,
39
code_hash,
40
onchain_decimals,
41
raw_balance,
42
format_units(raw_balance, decimals) AS balance,
43
raw_allowance,
44
format_units(raw_allowance, decimals) AS allowance
45
FROM snapshot
46
ORDER BY symbol;
Notebook ready in readonly mode.

Caller-sensitive quote sanity

V4Quoter-style reads are scalar eth_call sanity checks whose result may depend on caller, block state, hooks, and revert behavior.

Materialize the returned STRUCT in a CTE before ranking or formatting, so null/error handling is explicit before downstream analysis.

  • Use raw_call/call_decode or read_contract_multicall when you need ok/reverted/error diagnostics.
  • Treat quote rows as caller-sensitive read evidence, not a promise of execution, MEV protection, settlement, or future fill.
  • Keep exact raw units for ranking; format only the final presentation columns.

Named parameters · RPC required

1
-- Scalar V4Quoter-style sanity rows; materialize the returned struct first
2
WITH quote_inputs(amount_in, zero_for_one) AS (
3
VALUES
4
(5::UINT256 * 1000000000000000000::UINT256, true),
5
(20::UINT256 * 1000000000000000000::UINT256, true),
6
(100::UINT256 * 1000000000000000000::UINT256, true)
7
),
8
quotes AS (
9
SELECT
10
amount_in,
11
zero_for_one,
12
read_contract(
13
$client,
14
'0x52f0e24d1c21c8a0cb1e5a5dd6198556bd9e1203'::ADDRESS,
15
'[{"type":"function","name":"quoteExactInputSingle","inputs":[{"name":"params","type":"tuple","components":[{"name":"poolKey","type":"tuple","components":[{"name":"currency0","type":"address"},{"name":"currency1","type":"address"},{"name":"fee","type":"uint24"},{"name":"tickSpacing","type":"int24"},{"name":"hooks","type":"address"}]},{"name":"zeroForOne","type":"bool"},{"name":"exactAmount","type":"uint128"},{"name":"hookData","type":"bytes"}]}],"outputs":[{"name":"amountOut","type":"uint256"},{"name":"gasEstimate","type":"uint256"}]}]'::JSON,
16
'quoteExactInputSingle',
17
'{"block_number":21000000,"on_error":"null"}'::JSON,
18
{
19
'poolKey': {
20
'currency0': '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
21
'currency1': '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS,
22
'fee': 200::UINT24,
23
'tickSpacing': 4::INT24,
24
'hooks': '0x0000000000000000000000000000000000000000'::ADDRESS
25
},
26
'zeroForOne': zero_for_one,
27
'exactAmount': amount_in,
28
'hookData': ''::BYTES
29
}
30
) AS quote
31
FROM quote_inputs
32
)
33
SELECT
34
amount_in,
35
zero_for_one,
36
quote IS NOT NULL AS quote_ok,
37
(quote).amountOut AS amount_out,
38
(quote).gasEstimate AS gas_estimate
39
FROM quotes
40
ORDER BY amount_in;
Notebook ready in readonly mode.

Which ABI helper should I use?

  • Use read_contract when you want an ABI-aware eth_call with a typed SQL result.
  • Use encode_function_data when you need calldata for simulation, transaction preparation, or raw eth_call.
  • Use raw_call when you want raw eth_call return bytes.
  • Use call_decode when you already have raw return bytes and want ABI-derived output columns.
  • Use event_signature when you need topic0 for a known event signature.
  • Use event_decode_json when you already have raw log topics and data.

Constant ABI vs dynamic ABI

read_contract must know its SQL result type while the query is planned.

Use a constant ABI literal when the ABI is fixed in the query; use the options + return_schema overload when abi comes from a column, CTE, or join.

Named parameters · RPC required

1
SELECT read_contract(
2
$client,
3
'0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
4
'[{"type":"function","name":"balanceOf","inputs":[{"type":"address"}],"outputs":[{"type":"uint256"}]}]'::JSON,
5
'balanceOf',
6
'{}'::JSON,
7
'uint256',
8
'0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS
9
) AS balance;
Notebook ready in readonly mode.

Additional overloads

read_contract(CLIENT, ADDRESS, JSON, VARCHAR, JSON, ...args) # Immediate Read · RPC required

Adds block_tag/block_number override via options parameter. Options can be dynamic (non-constant) to query different blocks per row. ABI must still be literal or foldable because output types are fixed during planning. Use options={'blockNumber': '0x...'} or {'block_number': 123} or {'block_tag': 'finalized'}.

Inputs

NameTypeUse
clientCLIENT

Readable EVM state represented by a live, pinned, or execution CLIENT.

requiredpositional
toADDRESS

Contract address to call.

requiredpositional
abiJSON

Literal or foldable JSON ABI used for argument encoding and return typing.

requiredpositional
function_nameVARCHAR

Constant ABI function name.

requiredpositional
optionsJSON

JSON options. Default block is latest; block_number/blockNumber overrides block_tag; on_error/onError is raise or null; rpc_batch_size must be >= 1.

requiredpositional
Showing fewer

Returns

Name Type
result dynamic ABI-derived scalar or STRUCT

A function with one output returns that value directly. A function with multiple outputs returns a STRUCT with fields named from ABI outputs, or output0, output1, and so on for unnamed outputs.

Overload examples

Named parameters · RPC required

1
-- Read historical price at specific block
2
WITH blocks AS (
3
SELECT block_number, json_object('blockNumber', concat('0x', to_hex(block_number))) AS opts
4
FROM generate_series(21000000, 21000010) AS t(block_number)
5
)
6
SELECT
7
block_number,
8
read_contract(
9
$client,
10
'0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419'::ADDRESS, -- Ethereum mainnet Chainlink ETH/USD feed
11
'[{"inputs":[],"name":"latestAnswer","outputs":[{"type":"int256"}],"stateMutability":"view","type":"function"}]'::JSON,
12
'latestAnswer',
13
opts -- dynamic options from CTE
14
) AS price
15
FROM blocks;
read_contract(CLIENT, ADDRESS, JSON, VARCHAR, JSON, VARCHAR, ...args) # Immediate Read · RPC required

Uses options for dynamic block overrides and return_schema for dynamic ABI reads. return_schema='raw' returns raw bytes as BYTES; one Solidity type returns a typed scalar; comma-separated Solidity types return a typed STRUCT. For multiple outputs, prefer named schema entries such as 'amountOut:uint256,sqrtPriceX96After:uint256' so result columns have stable names. Dynamic to/options/args are supported, and use NULL or '{}'::JSON for options when no block override is needed.

Inputs

NameTypeUse
clientCLIENT

Readable EVM state represented by a live, pinned, or execution CLIENT.

requiredpositional
toADDRESS

Contract address to call.

requiredpositional
abiJSON

Literal or foldable JSON ABI used for argument encoding and return typing.

requiredpositional
function_nameVARCHAR

Constant ABI function name.

requiredpositional
optionsJSON

JSON options. Default block is latest; block_number/blockNumber overrides block_tag; on_error/onError is raise or null; rpc_batch_size must be >= 1.

requiredpositional
Showing fewer

Returns

Name Type
result ANY

return_schema-declared scalar, STRUCT, or raw BYTES. return_schema='raw' returns raw BYTES data. A single declared Solidity type such as 'uint256' returns that value directly. Multiple outputs should use named entries such as 'amountOut:uint256,sqrtPriceX96After:uint256', which returns a STRUCT with those field names. Unnamed entries such as 'uint256,uint256' are accepted but fall back to field_0, field_1, and so on.

Overload examples

Named parameters · RPC required

1
-- Dynamic ABI from a CTE needs explicit return_schema
2
WITH abis AS (
3
SELECT '[
4
{
5
"type": "function",
6
"name": "balanceOf",
7
"stateMutability": "view",
8
"inputs": [{ "name": "account", "type": "address" }],
9
"outputs": [{ "name": "", "type": "uint256" }]
10
}
11
]'::JSON AS abi
12
)
13
SELECT read_contract(
14
$client,
15
'0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS, -- Ethereum mainnet WETH
16
'[{"type":"function","name":"balanceOf","inputs":[{"type":"address"}],"outputs":[{"type":"uint256"}]}]'::JSON,
17
'balanceOf',
18
'{}'::JSON, -- options (use NULL or {} if not needed)
19
'uint256', -- return_schema: 'raw' | 'uint256' | 'amountOut:uint256'
20
'0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS -- function arguments
21
) AS balance FROM abis; -- Returns: UINT256 (exact type you specify)
22
 
23
-- For a dynamic ABI method with multiple Solidity outputs, declare named
24
-- return_schema fields so the STRUCT keys match the contract result.
25
-- Example: 'amountOut:uint256,sqrtPriceX96After:uint256'
26
-- returns STRUCT(amountOut UINT256, sqrtPriceX96After UINT256).
read_contract(ADDRESS, JSON, VARCHAR, ...args) # Read Builder · RPC required

Builds an ABI-aware contract READ from the function definition and arguments; it does not execute the call.

Inputs

Name Type Use
to ADDRESS

Contract address that the later observation should call.

 required positional
abi JSON

JSON ABI containing the selected function definition.

 required positional
function_name VARCHAR

ABI function name used to encode calldata and describe the later result.

 required positional
...args dynamic ANY

Pass one SQL value per ABI input. Values are normalized immediately into canonical calldata.

 positional

Returns

Name Type
read READ

Contract READ. Returns a canonical ABI-encoded READ specification for a later contract observation.

Examples

Named parameters · RPC required

1
-- Pinned ERC-20/account snapshot at Ethereum block 21000000
2
WITH params AS (
3
SELECT
4
'0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS AS account,
5
'0x111111125421cA6dc452d289314280a0f8842A65'::ADDRESS AS spender,
6
21000000::BIGINT AS block_number
7
),
8
tokens(symbol, token_address, decimals) AS (
9
VALUES
10
('WETH', '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS, 18),
11
('USDC', '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS, 6)
12
),
13
snapshot AS (
14
SELECT
15
t.symbol,
16
t.token_address,
17
keccak256(code_at($client, t.token_address, p.block_number)) AS code_hash,
18
read_contract_at(
19
$client, t.token_address,
20
'[{"type":"function","name":"decimals","inputs":[],"outputs":[{"name":"decimals","type":"uint8"}]}]'::JSON,
21
'decimals', p.block_number
22
) AS onchain_decimals,
23
read_contract_at(
24
$client, t.token_address,
25
'[{"type":"function","name":"balanceOf","inputs":[{"name":"account","type":"address"}],"outputs":[{"name":"balance","type":"uint256"}]}]'::JSON,
26
'balanceOf', p.block_number, p.account
27
) AS raw_balance,
28
read_contract_at(
29
$client, t.token_address,
30
'[{"type":"function","name":"allowance","inputs":[{"name":"owner","type":"address"},{"name":"spender","type":"address"}],"outputs":[{"name":"allowance","type":"uint256"}]}]'::JSON,
31
'allowance', p.block_number, p.account, p.spender
32
) AS raw_allowance,
33
t.decimals
34
FROM tokens t, params p
35
)
36
SELECT
37
symbol,
38
token_address,
39
code_hash,
40
onchain_decimals,
41
raw_balance,
42
format_units(raw_balance, decimals) AS balance,
43
raw_allowance,
44
format_units(raw_allowance, decimals) AS allowance
45
FROM snapshot
46
ORDER BY symbol;
Notebook ready in readonly mode.

Named parameters · RPC required

1
-- Scalar V4Quoter-style sanity rows; materialize the returned struct first
2
WITH quote_inputs(amount_in, zero_for_one) AS (
3
VALUES
4
(5::UINT256 * 1000000000000000000::UINT256, true),
5
(20::UINT256 * 1000000000000000000::UINT256, true),
6
(100::UINT256 * 1000000000000000000::UINT256, true)
7
),
8
quotes AS (
9
SELECT
10
amount_in,
11
zero_for_one,
12
read_contract(
13
$client,
14
'0x52f0e24d1c21c8a0cb1e5a5dd6198556bd9e1203'::ADDRESS,
15
'[{"type":"function","name":"quoteExactInputSingle","inputs":[{"name":"params","type":"tuple","components":[{"name":"poolKey","type":"tuple","components":[{"name":"currency0","type":"address"},{"name":"currency1","type":"address"},{"name":"fee","type":"uint24"},{"name":"tickSpacing","type":"int24"},{"name":"hooks","type":"address"}]},{"name":"zeroForOne","type":"bool"},{"name":"exactAmount","type":"uint128"},{"name":"hookData","type":"bytes"}]}],"outputs":[{"name":"amountOut","type":"uint256"},{"name":"gasEstimate","type":"uint256"}]}]'::JSON,
16
'quoteExactInputSingle',
17
'{"block_number":21000000,"on_error":"null"}'::JSON,
18
{
19
'poolKey': {
20
'currency0': '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
21
'currency1': '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS,
22
'fee': 200::UINT24,
23
'tickSpacing': 4::INT24,
24
'hooks': '0x0000000000000000000000000000000000000000'::ADDRESS
25
},
26
'zeroForOne': zero_for_one,
27
'exactAmount': amount_in,
28
'hookData': ''::BYTES
29
}
30
) AS quote
31
FROM quote_inputs
32
)
33
SELECT
34
amount_in,
35
zero_for_one,
36
quote IS NOT NULL AS quote_ok,
37
(quote).amountOut AS amount_out,
38
(quote).gasEstimate AS gas_estimate
39
FROM quotes
40
ORDER BY amount_in;
Notebook ready in readonly mode.

Local SQL

1
SELECT read_contract('0x0000000000000000000000000000000000000001'::ADDRESS, '[{"type":"function","name":"ping","inputs":[],"outputs":[]}]'::JSON, 'ping');
Notebook ready in readonly mode.

Related functions

Category and tags

Category
Chain reads
Tag
RPC
Tag
Simulate