> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need.

# Functions

Chains store most of what happens on them as raw bytes: the events contracts emit, the calls transactions make, and
the values programs return. Amp's SQL has functions that decode those bytes into columns you can read, filter, and
aggregate, and that convert addresses and token amounts to the forms people use. When they don't cover what you need,
a [derived dataset](/datasets/derived-datasets) can define its own, in [JavaScript](/querying/functions/javascript).

These come on top of the functions of [DataFusion](https://datafusion.apache.org/user-guide/sql/index.html), Amp's
query engine, like `date_trunc`, `concat`, and `array_agg`, and of `block_num()` and `ts()`, which
[SQL](/querying/sql#block-numbers-and-timestamps) describes.

## Prerequisites

* A [connection](/querying/connecting) to a query server. The example uses `ampsql`.
* To run the example, the [example datasets](/querying/connecting#example-datasets).

## Built-in functions

Built-in functions work in every query, batch or [streaming](/querying/streaming-queries), and in the SQL of derived
datasets. All but `eth_call` work on the data alone, with no setup.

### EVM

For Ethereum and the chains that run its virtual machine, like Base and Arbitrum.

| Function                                                         | What it does                                                    |
| ---------------------------------------------------------------- | --------------------------------------------------------------- |
| [`evm_decode_hex`](/querying/functions/evm#evm_decode_hex)       | Turns an address or a hash into a `0x` string.                  |
| [`evm_encode_hex`](/querying/functions/evm#evm_encode_hex)       | Turns a `0x` string into an address or a hash.                  |
| [`evm_topic`](/querying/functions/evm#evm_topic)                 | Computes an event's `topic0` from its signature.                |
| [`evm_decode_log`](/querying/functions/evm#evm_decode_log)       | Decodes an event log's parameters.                              |
| [`evm_decode_params`](/querying/functions/evm#evm_decode_params) | Decodes the arguments of a function call.                       |
| [`evm_encode_params`](/querying/functions/evm#evm_encode_params) | Encodes a function call: its selector, then its arguments.      |
| [`evm_decode_type`](/querying/functions/evm#evm_decode_type)     | Decodes an ABI-encoded value of any Solidity type.              |
| [`evm_encode_type`](/querying/functions/evm#evm_encode_type)     | ABI-encodes a value as a Solidity type.                         |
| [`shift_units`](/querying/functions/evm#shift_units)             | Moves a number's decimal point, to convert token amounts.       |
| [`eth_call`](/querying/functions/eth-call)                       | Calls a contract's read-only function, through the chain's RPC. |

`eth_call` is the only function that reaches outside Amp: it needs an [EVM RPC provider](/providers/evm-rpc) for the
chain it calls, and each call is a request to that provider.

### Solana

| Function                                                                | What it does                                                         |
| ----------------------------------------------------------------------- | -------------------------------------------------------------------- |
| [`borsh_decode`](/querying/functions/solana#borsh_decode)               | Decodes Borsh bytes, like instruction data, with a layout you write. |
| [`anchor_decode_event`](/querying/functions/solana#anchor_decode_event) | Decodes an Anchor program's event, using the program's IDL.          |

### Substrate

For chains built with Substrate, like Polkadot and Bittensor.

| Function                                                     | What it does                                                                  |
| ------------------------------------------------------------ | ----------------------------------------------------------------------------- |
| [`scale_decode`](/querying/functions/substrate#scale_decode) | Decodes SCALE bytes, like events, call arguments, and storage, with a layout. |
| [`ss58_encode`](/querying/functions/substrate#ss58_encode)   | Turns a 32-byte account ID into an SS58 address.                              |
| [`ss58_decode`](/querying/functions/substrate#ss58_decode)   | Turns an SS58 address into its 32-byte account ID.                            |

### Stellar

| Function                                                                   | What it does                                                           |
| -------------------------------------------------------------------------- | ---------------------------------------------------------------------- |
| [`stellar_scval_type`](/querying/functions/stellar#stellar_scval_type)     | Returns the kind of value a Soroban `ScVal` holds.                     |
| [`stellar_decode_scval`](/querying/functions/stellar#stellar_decode_scval) | Decodes a Soroban `ScVal` to a type you choose, or to JSON.            |
| [`stellar_xdr_to_json`](/querying/functions/stellar#stellar_xdr_to_json)   | Decodes a Stellar XDR record, like an event or a transaction, to JSON. |

The Solana, Substrate, and Stellar functions are experimental: how you call them may change between releases.

## Combine functions

Functions nest like any others. This decodes USDC's `Transfer` events, then adds up each block's transfers, in USDC
rather than its smallest unit:

```sql
WITH transfers AS (
  SELECT block_num,
    evm_decode_log(topic1, topic2, topic3, data, 'Transfer(address indexed from, address indexed to, uint256 value)') AS transfer
  FROM "acme/eth_mainnet".logs
  WHERE address = evm_encode_hex('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48')
    AND topic0 = evm_topic('Transfer(address indexed from, address indexed to, uint256 value)')
)
SELECT block_num, COUNT(*) AS transfers, shift_units(SUM(CAST(transfer.value AS DECIMAL(38, 0))), -6) AS usdc
FROM transfers
GROUP BY block_num
ORDER BY block_num
LIMIT 3
```

```text [Output]
+-----------+-----------+----------------+
| block_num | transfers | usdc           |
+-----------+-----------+----------------+
| 24000000  | 73        | 4382117.206181 |
| 24000001  | 35        | 39976.93911    |
| 24000002  | 49        | 45952.417049   |
+-----------+-----------+----------------+
```

* `evm_topic` turns the event's signature into the `topic0` its logs have, and `evm_encode_hex` turns USDC's address
  into bytes, so that the filter compares bytes with bytes.
* `evm_decode_log` decodes each log into a struct, whose fields the outer query reads, like `transfer.value`.
* A `uint256` decodes to text, since no SQL number type holds all of its values, so the query casts it to a decimal to
  add it up. `shift_units` then moves the decimal point by USDC's 6 decimals.

## Arguments

Decoding functions take two kinds of arguments:

* **The data**: usually a column, like a log's `data` or an instruction's bytes.
* **What it holds**: a Solidity signature, a layout, an IDL, or a type. It must be a string literal, written in the
  query: Amp reads it before running the query, to work out the result's type. A column or a computed value fails the
  query.

## Bad input

Chains hold data that doesn't always match what you expect: a log from another contract with the same event name, a
truncated payload. So that one such row doesn't stop a query over millions:

* **Data that doesn't decode** returns NULL, or NULL fields, for that row. The query keeps going. Filter the rows you
  decode, as the example does with `address` and `topic0`, and check for NULLs where it matters. `evm_decode_type` is
  the exception: a row it can't decode fails the whole query, unless you
  [decode inside a `CASE`](/querying/functions/evm#decode-data-that-may-not-match).
* **Arguments that are wrong for every row** fail the query before it runs: a signature or layout that doesn't parse, an
  argument of the wrong type, or a column where a literal is expected. The error names the function and the problem.

Each function's section lists what it returns for bad data, and its errors. `eth_call` differs: a call that fails
returns its error message, alongside the data (see [handle failed
calls](/querying/functions/eth-call#handle-failed-calls)).

## JavaScript functions

When the built-in functions don't do what you need, write a function in JavaScript, in the manifest of a derived
dataset. Its tables call it as `self.name()`, and other queries through the dataset's name. They run in a sandbox, with
no network or file access. They're experimental. See [JavaScript functions](/querying/functions/javascript).