TSQL Api Reference
The below reference informs you on what features are available in our Schema in order to enable you to write optimal TSQL.
Functions:
block_timestamp
block_timestamp(block_number bigint)
Returns the timestamp of a block.
Example Usage:
select block_timestamp(1)
BLOCK_TIMESTAMP |
---|
2015-07-30T15:26:28+00:00 |
get_chain_id
get_chain_id()
Returns the chain id of the current network.
Example Usage:
select get_chain_id()
GET_CHAIN_ID |
---|
1 |
Note: This was run with Ethereum selected
get_historical_block_number(bigint interval timestamp with time zone)
get_historical_block_number(start bigint, inter interval, _now timestamp with time zone default now()) returns bigint
Returns the block number given an interval of time in the past and the start block.
Example Usage:
select get_historical_block_number(1700000, interval '1 days')
GET_HISTORICAL_BLOCK_NUMBER |
---|
1692846 |
get_historical_block_number(interval timestamp with time zone)
get_historical_block_number(inter interval, _now timestamp with time zone default now()) returns bigint
Returns the block number given an interval of time in the past.
Example Usage:
select get_historical_block_number(interval '1 day')
GET_HISTORICAL_BLOCK_NUMBER |
---|
18534695 |
get_schema_name
get_schema_name() returns character varying
Returns the currently used blockchain network for monitoring.
Example Usage:
select get_schema_name()
GET_SCHEMA_NAME |
---|
ethereum |
last_block_number
last_block_number() returns bigint
Returns the last block number in the network.
Example Usage:
select last_block_number()
LAST_BLOCK_NUMBER |
---|
18534695 |
safe_block_timestamp
safe_block_timestamp(block_num bigint) returns bigint
Returns the unix timestamp of a block.
Example Usage:
select safe_block_timestamp(1)
SAFE_BLOCK_TIMESTAMP |
---|
1438269988 |
to_usd_value
to_usd_value(amount numeric, token_address bytea) returns numeric
Returns the USD value of an amount of token.
Example Usage:
select to_usd_value(1e18, '\x6b175474e89094c44da98b954eedeac495271d0f')
TO_USD_VALUE |
---|
1 |
tx_hash
tx_hash(block_number bigint, tx_index integer) returns bytea
Returns the transaction hash given a block number and transaction index.
Example Usage:
select tx_hash(1500000, 0)
TX_HASH |
---|
be03790872e51ef0ffe1b5d741bdaa09b4e158a579f721da0725ace53b55b87f |
Gadgets:
block
{{ block(duration: Optional['5m', '1h', '24h', '3d'] = '24h', start_time: Optional['YYYY-MM-DDTHH:MM:SS'] = 'now()') }}
Returns an interval of blocks from block.
Example Usage:
select block_number from {{ block('5m') }} _
BLOCK_NUMBER |
---|
18542144 |
18542145 |
18542146 |
18542147 |
... |
contracts
{{ contracts(protocols: Optional[list[str]] = None) }}
Returns a list of contracts from contracts.
Example Usage:
select address from {{ contracts(protocols=['Maker']) }} _
ADDRESS |
---|
004b750a71f1aa80150b18ecd510e6abf7e25480 |
00efe35880071c16832b3ad135885e7746adf889 |
010df1cd88f927c8a77f39265c3eac634ec072c0 |
... |
eth_call
{{ eth_call('address.function(args...)', outputs='types...') }}
Call any function on the currently active network, returns the outputs as defined by the user.
Example Usage:
{{eth_call('0x6B175474E89094C44Da98b954EedeAC495271d0F.name()', outputs='string')}}
Result |
---|
Dai Stablecoin |
Example Usage with args:
WITH a(x) AS (
SELECT '0x0000000000000000000000000000000000000000'
)
select {{eth_call('0x6B175474E89094C44Da98b954EedeAC495271d0F.balanceOf(address x)', outputs='uint256 out')}} as out
from a
OUT |
---|
9.236207899175045e+21 |
logs
{{ logs(inputs: optional[str] = None, duration: Optional['5m', '1h', '24h', '3d'] = '24h') }}
Returns a list of logs from logs.
Example Usage:
select address from {{ logs(inputs="Transfer(address indexed from,address indexed to,uint256 value)") }} _ limit 1
ADDRESS |
---|
a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 |
outer_transaction
{{ outer_transaction(duration: Optional['5m', '1h', '24h', '3d'] = '24h', , start_time: Optional['YYYY-MM-DDTHH:MM:SS'] = 'now()') }}
Returns a list of transactions from outer_transaction.
Example Usage:
select tx_hash from {{ outer_transaction('5m') }} _
TX_HASH |
---|
ae7eece7472193a630676e513d73dfac5fb4c38ff1c62141ac7ea3a04a40ee83 |
b61f251d9497d312503e8036cb737f8ee47d8b011a6a0494b66523243ec2a902 |
c168b67816c2da913b628693b65ca71ea4d839c810bf9759c94d4820d5e07a25 |
... |
token_ledger
{{ token_ledger(duration: Optional['5m', '1h', '24h', '3d'] = '24h', start_time: Optional['YYYY-MM-DDTHH:MM:SS'] = 'now()') }}
Returns a list of token_ledger entries from token_ledger.
Example Usage:
select token_address from {{ token_ledger('5m') }} _
TOKEN_ADDRESS |
---|
eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee |
dac17f958d2ee523a2206206994597c13d831ec7 |
... |
token_transfers
{{ token_transfers(duration: Optional['5m', '1h', '24h', '3d'] = '24h', start_time: Optional['YYYY-MM-DDTHH:MM:SS'] = 'now()') }}
Returns a list of token_transfers from token_transfers.
Example Usage:
select token_address from {{ token_transfers('5m') }} _
TOKEN_ADDRESS |
---|
eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee |
dac17f958d2ee523a2206206994597c13d831ec7 |
... |
transaction_detail
{{ transaction_detail(inputs: optional[str] = None, outputs: optional[str] = None, duration: Optional['5m', '1h', '24h', '3d'] = '24h') }}
Returns a list of transaction_detail entries from transaction_detail.
Example Usage:
select gas_used from {{ transaction_detail('transmit(bytes,bytes32[],bytes32[],bytes32)', duration='5m') }} _
GAS_USED |
---|
144515 |
144515 |
144427 |
... |
is_ancestor
{{ is_ancestor(ancestor_tx: transaction_detail, descendant_tx: transaction_detail) }}
returns all a list of all transactions that are descendants of the ancestor_tx including the ancestor_tx.
Example Usage:
select td1.block_number, td1.tx_index, td1.vm_step_start from {{ transaction_detail() }} td1
join {{ transaction_detail() }} td2 on {{ is_ancestor("td1", "td2") }} order by td1.block_number desc, td1.tx_index desc, td1.vm_step_start desc
BLOCK_NUMBER | TX_INDEX | VM_STEP_START |
---|---|---|
18563576 | 109 | 2724 |
18563576 | 109 | 0 |
18563576 | 101 | 3441 |
... |
is_parent
{{ is_parent(parent_tx: transaction_detail, child_tx: transaction_detail) }}
is_parent is a strict subset of is_ancestor, that can be considered as is_caller for the stack frame. transaction_detail.
Example Usage:
select td1.block_number, td1.tx_index, td1.vm_step_start from {{ transaction_detail() }} td1
join {{ transaction_detail() }} td2 on {{ is_parent("td1", "td2") }} order by td1.block_number desc, td1.tx_index desc, td1.vm_step_start desc
BLOCK_NUMBER | TX_INDEX | VM_STEP_START |
---|---|---|
18563576 | 109 | 2724 |
18563576 | 109 | 0 |
18563576 | 101 | 3441 |
... |
Tables:
Keyword reference:
Address
: '\x' + 40 hex charactersSignature
: Textual format of the function signatureSelector
: '\x' + 8 hex characters
Table types:
Addresses
- key data type:
Address
- Involved Tables
Blocks
- Unique key:
block_number
- Involved Tables:
Contracts
- Unique key:
md5_bytecode
- Involved Tables:
Selectors
- Key data type:
Selector
- Involved Tables:
Tokens
- Unique key:
token_address
- Involved Tables:
Transactions
- Unique key:
block_number
,tx_index
- Involved Tables:
Network Tables:
block
select * from block
Contains information about the blocks in a network.
Columns
gas_used::numeric
- The amount of gas used by the block.ts::timestamp with time zone
- Timestamp of when the block was mined.coinbase::bytea
- The address of the miner of the block.transactions::bytea[]
- Transactions included in the block.base_fee_per_gas::numeric
- The base fee per gas in the block.gas_limit::numeric
- The gas limit of the block.parent_hash::bytea
- The hash of the parent block.block_number::bigint
- The number of the block.block_hash::bytea
- The hash of the block._ts::timestamp with time zone
- Timestamp of the block data entry.extra_data::bytea
- Extra data included in the block.size::integer
- The size of the block in bytes.nonce::numeric
- The nonce that was used to mine the block.difficulty::numeric
- The difficulty of the block.