Suggested
View this page as Markdown

read_contract

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

Scalar functionLive RPC RPCProgram CompilerSimulate

Example

Live RPC

finalized selects the finalized block at execution time; use an explicit block number/hash to identify a fixed snapshot.

-- WETH balance of vitalik.eth in raw units
SELECT read_contract(
  $client, '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS,
  '[{"type":"function","name":"balanceOf","inputs":[{"name":"account","type":"address"}],"outputs":[{"name":"balance","type":"uint256"}]}]'::JSON,
  'balanceOf', '0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS
) AS raw_balance;

API reference

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

Derives the SQL return type and field names from a literal or foldable ABI. function_name must also be literal or foldable. The block selector is latest.

Inputs

Name Type Use
client CLIENT

Live, pinned, or retained execution CLIENT. Pinned and execution state require the resolved block selector to be latest and reject other selectors.

 required positional
to ADDRESS

Contract address to call.

 required positional
abi JSON

Literal or foldable JSON ABI used for argument encoding and output types.

 required positional
function_name VARCHAR

Literal or foldable 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.
read_contract(CLIENT, ADDRESS, JSON, VARCHAR, JSON, ...args) # Immediate Read · RPC required

Allows per-row options while deriving the return type from a literal or foldable ABI. function_name must also be literal or foldable.

Inputs

Name Type Use
client CLIENT

Live, pinned, or retained execution CLIENT. Pinned and execution state require the resolved block selector to be latest and reject other selectors.

 required positional
to ADDRESS

Contract address to call.

 required positional
abi JSON

Literal or foldable JSON ABI used for argument encoding and output types.

 required positional
function_name VARCHAR

Literal or foldable ABI function name.

 required positional
options JSON

JSON object. The block defaults to latest. block_number is preferred over blockNumber, and both take precedence over block_tag; a null block_number gives NULL without a call. on_error is preferred over onError and accepts raise, null (reverts and decode failures, not transport failures) or null_on_failure (transport failures too, for a caller that records them as unread). rpc_batch_size defaults to 100 and must be at least 1. Any other key raises.

 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.

Applicable shared examples: Finalized ERC-20 balances

Overload examples

Live RPC 

-- Read a historical price across per-row block options
WITH blocks AS (
  SELECT block_number, json_object('blockNumber', concat('0x', to_hex(block_number))) AS opts
  FROM generate_series(21000000, 21000010) AS t(block_number)
)
SELECT
  block_number,
  read_contract(
    $client,
    '0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419'::ADDRESS, -- Ethereum mainnet Chainlink ETH/USD feed
    '[{"inputs":[],"name":"latestAnswer","outputs":[{"type":"int256"}],"stateMutability":"view","type":"function"}]'::JSON,
    'latestAnswer',
    opts -- per-row block options from the CTE
  ) AS price
FROM blocks;
read_contract(CLIENT, ADDRESS, JSON, VARCHAR, JSON, VARCHAR, ...args) # Immediate Read · RPC required

Uses a literal or foldable return_schema to fix the SQL return type. The ABI may vary by row in raw or explicit-schema mode; function_name and return_schema must remain foldable. raw returns BYTES, one declared type returns a scalar, and multiple declared fields return a STRUCT named by return_schema.

Inputs

Name Type Use
client CLIENT

Live, pinned, or retained execution CLIENT. Pinned and execution state require the resolved block selector to be latest and reject other selectors.

 required positional
to ADDRESS

Contract address to call.

 required positional
abi JSON

JSON ABI used for argument encoding. It may vary by row in raw or explicit-schema mode; otherwise it must be literal or foldable.

 required positional
function_name VARCHAR

Literal or foldable ABI function name.

 required positional
options JSON

JSON object. The block defaults to latest. block_number is preferred over blockNumber, and both take precedence over block_tag; a null block_number gives NULL without a call. on_error is preferred over onError and accepts raise, null (reverts and decode failures, not transport failures) or null_on_failure (transport failures too, for a caller that records them as unread). rpc_batch_size defaults to 100 and must be at least 1. Any other key raises.

 required positional
return_schema VARCHAR

Literal or foldable output schema: raw, one type, or comma-separated fields such as amount:uint256. Unnamed fields use field_0, field_1, and so on.

 required positional
...args dynamic ANY

Pass one SQL value per ABI input. The ABI determines argument count and casts, and may be dynamic in this overload. return_schema supplies the planning-time output contract.

 positional

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

Live RPC 

-- Dynamic ABI from a CTE needs an explicit return_schema
WITH abis AS (
  SELECT '[
    {
      "type": "function",
      "name": "balanceOf",
      "stateMutability": "view",
      "inputs":  [{ "name": "account", "type": "address" }],
      "outputs": [{ "name": "", "type": "uint256" }]
    }
  ]'::JSON AS abi
)
SELECT read_contract(
  $client,
  '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS, -- Ethereum mainnet WETH
  abi,
  'balanceOf',
  '{}'::JSON, -- options; use NULL or {} when no override is needed
  'uint256', -- return_schema fixes the SQL output type
  '0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS -- function argument
) AS balance FROM abis;
read_contract(ADDRESS, JSON, VARCHAR, ...args) # Read Builder

Selects an ABI function and encodes its arguments for a later isolated observation call.

Inputs

Name Type Use
to ADDRESS

Contract address that the later observation should call.

 required positional
abi JSON

Non-NULL JSON ABI used for function selection, input encoding, and output decoding.

 required positional
function_name VARCHAR

Function name, canonical signature, or supported selector literal resolved against the ABI and arguments.

 required positional
...args dynamic ANY

One non-NULL SQL value per ABI input, in order. Incompatible values and ambiguous overloads fail.

 positional

Returns

Name Type
read READ

Contract READ. Returns an ABI-aware READ with encoded calldata, output schema, zero caller, zero Wei value, and a 16,777,216 gas limit.

Overload examples

Runs locally 

SELECT read_contract('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS, -- Ethereum mainnet WETH
  '[{"type":"function","name":"balanceOf","inputs":[{"name":"account","type":"address"}],"outputs":[{"name":"balance","type":"uint256"}]}]'::JSON,
  'balanceOf', '0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS);

Guidance

Options

The default block selector is latest. block_number is preferred over blockNumber, and either takes precedence over block_tag.

on_error is preferred over onError. The default is raise; null converts reverts, ABI result-decode errors (including empty return data from an address without code) and decoded output-count mismatches to NULL. Transport, rate-limit and archive failures still raise, as do invalid options, arguments, ABI encoding, client-state errors and return_schema cast failures.

null_on_failure also gives NULL for a read the node could not answer, for a caller that records it as unread instead of reading NULL as a value.

A null block_number, or a NULL options value on a row, gives NULL without a call. An unknown option key raises.

rpc_batch_size defaults to 100 and must be an integer of at least 1.

  • Pinned and retained execution CLIENT values require the resolved block selector to be latest. read_contract_at and options that select another block are rejected for those states.

Live RPC

WITH tokens(symbol, token_address, holder, decimals) AS (
  VALUES
    ('WETH', '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS,
             '0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS, 18)
),
raw_balances AS (
  SELECT
    symbol,
    decimals,
    read_contract(
      $client,
      token_address,
      '[{"type":"function","name":"balanceOf","inputs":[{"name":"account","type":"address"}],"outputs":[{"name":"balance","type":"uint256"}]}]'::JSON,
      'balanceOf',
      '{"block_number":21000000}'::JSON,
      holder
    ) AS raw_balance
  FROM tokens
)
SELECT symbol, raw_balance, format_units(raw_balance, decimals) AS balance
FROM raw_balances
ORDER BY sort_key(raw_balance) DESC;

Pinned account snapshots

Use the same block number for every read in an account snapshot, and retain raw UINT256 values beside formatted display values.

Compute code hashes with code_at and keccak256 at that block when bytecode identity is part of the evidence.

  • Read decimals, balanceOf, allowance, and code hash in one table-shaped query.
  • Older block heights may require an archive-capable provider.

Compose an account snapshot

Retain raw values alongside formatted display values; all reads must use the same fixed block.

Live RPC

-- Pinned ERC-20 account snapshot at Ethereum block 21000000
WITH params AS (
  SELECT
    '0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS AS account,
    '0x111111125421cA6dc452d289314280a0f8842A65'::ADDRESS AS spender,
    21000000::BIGINT AS block_number
),
tokens(symbol, token_address, decimals) AS (
  VALUES
    ('WETH', '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS, 18),
    ('USDC', '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS, 6)
),
snapshot AS (
  SELECT
    t.symbol,
    t.token_address,
    keccak256(code_at($client, t.token_address, p.block_number)) AS code_hash,
    read_contract_at(
      $client, t.token_address,
      '[{"type":"function","name":"decimals","inputs":[],"outputs":[{"name":"decimals","type":"uint8"}]}]'::JSON,
      'decimals', p.block_number
    ) AS onchain_decimals,
    read_contract_at(
      $client, t.token_address,
      '[{"type":"function","name":"balanceOf","inputs":[{"name":"account","type":"address"}],"outputs":[{"name":"balance","type":"uint256"}]}]'::JSON,
      'balanceOf', p.block_number, p.account
    ) AS raw_balance,
    read_contract_at(
      $client, t.token_address,
      '[{"type":"function","name":"allowance","inputs":[{"name":"owner","type":"address"},{"name":"spender","type":"address"}],"outputs":[{"name":"allowance","type":"uint256"}]}]'::JSON,
      'allowance', p.block_number, p.account, p.spender
    ) AS raw_allowance,
    t.decimals
  FROM tokens t, params p
)
SELECT
  symbol,
  token_address,
  code_hash,
  onchain_decimals,
  raw_balance,
  format_units(raw_balance, decimals) AS balance,
  raw_allowance,
  format_units(raw_allowance, decimals) AS allowance
FROM snapshot
ORDER BY symbol;

Caller-sensitive quote checks

V4Quoter-style eth_call results can depend on caller, block state, hooks, and revert behavior.

Materialize the returned STRUCT before ranking or formatting so NULL and error handling are explicit.

  • Use raw_call/call_decode or read_contract_multicall when you need ok, reverted, or error diagnostics.
  • A quote is read evidence, not a promise of execution, MEV protection, settlement, or future fill.
  • Keep raw units for ranking and format only presentation columns.

Live RPC

-- V4Quoter-style sanity rows; materialize the returned STRUCT first
WITH quote_inputs(amount_in, zero_for_one) AS (
  VALUES
    (5::UINT256 * 1000000000000000000::UINT256, true),
    (20::UINT256 * 1000000000000000000::UINT256, true),
    (100::UINT256 * 1000000000000000000::UINT256, true)
),
quotes AS (
  SELECT
    amount_in,
    zero_for_one,
    read_contract(
      $client,
      '0x52f0e24d1c21c8a0cb1e5a5dd6198556bd9e1203'::ADDRESS,
      '[{"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,
      'quoteExactInputSingle',
      '{"block_number":21000000,"on_error":"null"}'::JSON,
      {
        'poolKey': {
          'currency0': '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
          'currency1': '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS,
          'fee': 200::UINT24,
          'tickSpacing': 4::INT24,
          'hooks': '0x0000000000000000000000000000000000000000'::ADDRESS
        },
        'zeroForOne': zero_for_one,
        'exactAmount': amount_in,
        'hookData': ''::BYTES
      }
    ) AS quote
  FROM quote_inputs
)
SELECT
  amount_in,
  zero_for_one,
  quote IS NOT NULL AS quote_ok,
  (quote).amountOut AS amount_out,
  (quote).gasEstimate AS gas_estimate
FROM quotes
ORDER BY amount_in;

ABI validation

Construction fails for malformed ABI, NULL arguments, missing or incompatible functions, ambiguous overloads, argument-count mismatches, or values that cannot be ABI-encoded.

Call defaults

The observation call uses the zero address as caller, zero Wei value, and a 16,777,216 gas limit; all observation calls are capped at 30,000,000 gas.

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.

Live RPC

SELECT read_contract(
  $client,
  '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
  '[{"type":"function","name":"balanceOf","inputs":[{"type":"address"}],"outputs":[{"type":"uint256"}]}]'::JSON,
  'balanceOf',
  '{}'::JSON,
  'uint256',
  '0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS
) AS balance;

Examples

Live RPC

-- Table-driven ERC-20 balances with raw and display values
WITH tokens(symbol, token_address, holder, decimals) AS (
  VALUES
    ('WETH', '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS,
             '0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS, 18),
    ('USDC', '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
             '0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS, 6)
),
balances AS (
  SELECT
    symbol,
    decimals,
    read_contract(
      $client,
      token_address,
      '[{
        "type": "function",
        "name": "balanceOf",
        "stateMutability": "view",
        "inputs":  [{ "name": "account", "type": "address" }],
        "outputs": [{ "name": "balance", "type": "uint256" }]
      }]'::JSON,
      'balanceOf',
      '{"block_tag":"finalized"}'::JSON,
      holder
    ) AS raw_balance
  FROM tokens
)
SELECT
  symbol,
  raw_balance,
  CASE symbol
    WHEN 'USDC' THEN format_units(raw_balance, 6)
    ELSE format_units(raw_balance, 18)
  END AS balance
FROM balances;