cardano-db-sync functions

cdbs-functions.psql

# curl -X POST    -H "Content-Type: application/json"    -d '{ "data": { "addresses" : ["stake_test1uz605p766mvsyrufagjw5fepqfp8x9ff2ty2hzdrjuvuj8g5efx4w","stake_test1uq3zf47elmdxp92wgmcx4lrkjrlts5fffs36c7dz02d7faqye6l9j"] } }'    -s "https://postgrest-api.testnet.dandelion.link/rpc/get_delegation_history_for_stake_address" | jq .
CREATE OR REPLACE FUNCTION get_delegation_history_for_stake_address(data json) RETURNS TABLE (epoch bigint, poolbech32 character varying, stake_address character varying, lovelace_rewards lovelace) AS $$
DECLARE
  addresses text[];
BEGIN
  addresses := (SELECT array_agg(replace(rec::text, '"', ''))
                FROM json_array_elements(data->'addresses') rec);
  RETURN QUERY (select reward.epoch_no, pool_hash.view as poolbech32, stake_address.view as stake_address, reward.amount as lovelace_rewards
              from reward inner join stake_address on reward.addr_id = stake_address.id
              inner join pool_hash on reward.pool_id = pool_hash.id
              where stake_address.view = ANY(addresses)
              order by epoch_no asc);
END; $$ LANGUAGE PLPGSQL IMMUTABLE;

# curl -X POST    -H "Content-Type: application/json"    -d '{ "data": { "addresses" : ["addr_test1vzep2se0nr849acwfnlpm2sa3sz726g6v78ej4sy9ewjprqmcl720","addr_test1qptw3z77j2vjhd45vdqhct46uvwucese43twz9afv0lt9h83lgv8qkdvg9akyn2yyrtp75sd6ejwl3m0f4qtve43ydnsckuvc4"] } }'    -s "https://postgrest-api.testnet.dandelion.link/rpc/get_tx_history_for_addresses" | jq .
CREATE OR REPLACE FUNCTION get_tx_history_for_addresses(data json) RETURNS TABLE (tx_hash text, block uinteger, tx_timestamp timestamp) AS $$
DECLARE
  addresses text[];
BEGIN
  addresses := (SELECT array_agg(replace(rec::text, '"', ''))
                FROM json_array_elements(data->'addresses') rec);
  RETURN QUERY (SELECT trim(txs.hash, '\\\\x') , txs.block_no, txs.time from (
    SELECT
      tx.id, tx.hash::text, block.block_no, block.hash::text as blockHash, block.time, tx.block_index
      FROM block
      INNER JOIN tx ON block.id = tx.block_id
      INNER JOIN tx_out ON tx.id = tx_out.tx_id
      WHERE tx_out.address = ANY(addresses)
    UNION
      SELECT DISTINCT
        tx.id, tx.hash::text, block.block_no, block.hash::text as blockHash, block.time, tx.block_index
        FROM block
        INNER JOIN tx ON block.id = tx.block_id
        INNER JOIN tx_in ON tx.id = tx_in.tx_in_id
        INNER JOIN tx_out ON (tx_in.tx_out_id = tx_out.tx_id) AND (tx_in.tx_out_index = tx_out.index)
        WHERE tx_out.address = ANY(addresses)
        ORDER BY time DESC
  ) AS txs);
END; $$ LANGUAGE PLPGSQL IMMUTABLE;

# eoe = balance at end of epoch, at epoch's last slot)
# curl -H "Content-Type: application/json" -X POST -d '{ "data": { "epoch": "105", "addresses" : ["addr_test1vzep2se0nr849acwfnlpm2sa3sz726g6v78ej4sy9ewjprqmcl720","addr_test1qptw3z77j2vjhd45vdqhct46uvwucese43twz9afv0lt9h83lgv8qkdvg9akyn2yyrtp75sd6ejwl3m0f4qtve43ydnsckuvc4"] } }' -s "https://postgrest-api.testnet.dandelion.link/rpc/get_eoe_balance_for_addresses" | jq .
CREATE OR REPLACE FUNCTION get_eoe_balance_for_addresses(data json) RETURNS TABLE (balance numeric, address character varying) AS $$
DECLARE
  addresses text[];
  epoch int;
BEGIN
  addresses := (SELECT array_agg(replace(rec::text, '"', ''))
                FROM json_array_elements(data->'addresses') rec);
  SELECT json_extract_path_text(data, 'epoch') INTO epoch AS tmp;
  RETURN QUERY (SELECT SUM(utxo_view.value), utxo_view.address FROM utxo_view
    INNER JOIN tx ON tx.id = utxo_view.tx_id
    INNER JOIN block ON block.id = tx.block_id
    WHERE utxo_view.address = ANY(addresses)
    AND block.slot_no <= (select get_last_slot_for_epoch(epoch))
    GROUP BY utxo_view.address);
END; $$ LANGUAGE PLPGSQL IMMUTABLE;

# curl -d epoch=100 -s 'https://postgrest-api.testnet.dandelion.link/rpc/get_last_slot_for_epoch' 
CREATE FUNCTION get_last_slot_for_epoch(epoch word64type default 1) RETURNS TABLE (slot_no uinteger) AS $$
BEGIN
  IF epoch IS NULL THEN
    select no into epoch from epoch order by no desc limit 1;
  END IF;
  RETURN QUERY (select block.slot_no from block where epoch_no = epoch order by slot_no desc limit 1);
END; $$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE FUNCTION get_metadatum() RETURNS TABLE (metadatum word64type) AS $$
BEGIN
  RETURN QUERY (select distinct key from tx_metadata order by key);
END; $$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE FUNCTION get_metadata(metadatum word64type default 0, epochs int[] default null) RETURNS TABLE (epoch uinteger, data jsonb) AS $$
BEGIN
  IF epochs IS NOT NULL THEN
    RETURN QUERY (select block.epoch_no, json as epoch from tx_metadata
                  inner join tx on tx_metadata.tx_id = tx.id
                  inner join block on tx.block_id = block.id
                  where key = metadatum and epoch_no = ANY(epochs)
                  order by epoch_no);
  ELSE
    RETURN QUERY (select block.epoch_no, json as epoch from tx_metadata
                  inner join tx on tx_metadata.tx_id = tx.id
                  inner join block on tx.block_id = block.id
                  where key = metadatum
                  order by epoch_no);
  END IF;
END; $$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE OR REPLACE FUNCTION get_valid_pools() RETURNS TABLE (pool_hash_id bigint, pool_bech32 character varying, vrf_key_hash character varying, pledge numeric(20,0), margin double precision, fixed_cost numeric(20,0)) AS $$
select distinct pool_hash.id, pool_hash.view, encode(pool_update.vrf_key_hash, 'hex'), pool_update.pledge, pool_update.margin, pool_update.fixed_cost from pool_update 
              inner join pool_hash on pool_update.hash_id = pool_hash.id
    where pool_update.registered_tx_id in (select max(pool_update.registered_tx_id) from pool_update group by hash_id)
    and not exists
      ( select * from pool_retire where pool_retire.hash_id = pool_update.hash_id
          and pool_retire.retiring_epoch <= (select max (epoch_no) from block)
          ) ;
$$ LANGUAGE SQL IMMUTABLE;

    CREATE OR REPLACE FUNCTION get_pools_dbsync_hash_id(_pool_bech32_ids text[] DEFAULT null) RETURNS TABLE (pool_dbsync_hash_id bigint, pool_bech32_id character varying, vrf_key_hash character varying) AS $$
    select distinct pool_hash.id, pool_hash.view, encode(pool_update.vrf_key_hash, 'hex') from pool_update
                  inner join pool_hash on pool_update.hash_id = pool_hash.id
                  where pool_update.registered_tx_id in (select max(pool_update.registered_tx_id) from pool_update group by hash_id)
                  and not exists
                  ( select * from pool_retire where pool_retire.hash_id = pool_update.hash_id
                    and pool_retire.retiring_epoch <= (select max (epoch_no) from block)
                  )
                  AND CASE
                  WHEN _pool_bech32_ids IS NULL THEN true
                  WHEN _pool_bech32_ids IS NOT NULL THEN pool_hash.view = ANY(SELECT UNNEST(_pool_bech32_ids))
                  END;
    $$ LANGUAGE SQL IMMUTABLE;


CREATE FUNCTION get_stake_distribution(pool_bech32 character varying default '', epoch int default null) RETURNS TABLE (pool character varying, lovelace numeric) AS $$
BEGIN
  IF epoch IS NULL THEN
    select no into epoch from epoch order by no desc limit 1;
  END IF;
  RETURN QUERY (select pool_hash.view, sum (amount) as lovelace from epoch_stake
              inner join pool_hash on epoch_stake.pool_id = pool_hash.id
              where epoch_no = epoch and pool_hash.view like '%' || pool_bech32 || '%'
              group by pool_hash.id);
END; $$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE FUNCTION get_delegation_history_for_stake_address(address character varying, epochs int[] default null) RETURNS TABLE (epoch_no bigint, delegated_pool character varying) AS $$
BEGIN
  IF epochs IS NOT NULL THEN
    RETURN QUERY (select delegation.active_epoch_no, pool_hash.view from delegation
              inner join stake_address on delegation.addr_id = stake_address.id
              inner join pool_hash on delegation.pool_hash_id = pool_hash.id
              where stake_address.view = address and active_epoch_no = ANY(epochs)
              order by active_epoch_no asc);
  ELSE
    RETURN QUERY (select delegation.active_epoch_no, pool_hash.view from delegation
              inner join stake_address on delegation.addr_id = stake_address.id
              inner join pool_hash on delegation.pool_hash_id = pool_hash.id
              where stake_address.view = address
              order by active_epoch_no asc);
  END IF;
END; $$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE FUNCTION get_rewards_history_for_stake_address(address character varying, epochs int[] default null) RETURNS TABLE (epoch_no bigint, delegated_pool character varying, lovelace lovelace) AS $$
BEGIN
  IF epochs IS NOT NULL THEN
    RETURN QUERY (select reward.epoch_no, pool_hash.view as delegated_pool, reward.amount as lovelace
              from reward inner join stake_address on reward.addr_id = stake_address.id
              inner join pool_hash on reward.pool_id = pool_hash.id
              where stake_address.view = address and reward.epoch_no = ANY(epochs)
              order by epoch_no asc);
  ELSE
    RETURN QUERY (select reward.epoch_no, pool_hash.view as delegated_pool, reward.amount as lovelace
              from reward inner join stake_address on reward.addr_id = stake_address.id
              inner join pool_hash on reward.pool_id = pool_hash.id
              where stake_address.view = address
              order by epoch_no asc);
  END IF;
END; $$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE FUNCTION get_blocknumbers_for_poolbech32(pool_bech32 character varying default '', epochs int[] default null) RETURNS TABLE (block_no uinteger, epoch_no uinteger, pool_view character varying) AS $$
BEGIN
  IF epochs IS NOT NULL THEN
    RETURN QUERY (select block.block_no, block.epoch_no, pool_hash.view as pool_view
              from block inner join slot_leader on block.slot_leader_id = slot_leader.id
              inner join pool_hash on slot_leader.pool_hash_id = pool_hash.id
              where block.epoch_no = ANY(epochs) and pool_hash.view like '%' || pool_bech32 || '%');
  ELSE
    RETURN QUERY (select block.block_no, block.epoch_no, pool_hash.view as pool_view
              from block inner join slot_leader on block.slot_leader_id = slot_leader.id
              inner join pool_hash on slot_leader.pool_hash_id = pool_hash.id
              where pool_hash.view like '%' || pool_bech32 || '%');
  END IF;
END; $$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE FUNCTION get_blockcount_for_poolbech32(pool_bech32 character varying default '', epochs int[] default null) RETURNS TABLE (epoch_no uinteger, block_count bigint) AS $$
BEGIN
  IF epochs IS NOT NULL THEN
    RETURN QUERY (select block.epoch_no, count (*) as block_count
                  from block inner join slot_leader on block.slot_leader_id = slot_leader.id
                  inner join pool_hash on slot_leader.pool_hash_id = pool_hash.id
                  where pool_hash.view = pool_bech32 and block.epoch_no = ANY(epochs)
                  group by block.epoch_no, pool_hash.view);
  ELSE
    RETURN QUERY (select block.epoch_no, count (*) as block_count
              from block inner join slot_leader on block.slot_leader_id = slot_leader.id
              inner join pool_hash on slot_leader.pool_hash_id = pool_hash.id
              where pool_hash.view = pool_bech32
              group by block.epoch_no, pool_hash.view);
  END IF; 
END; $$ LANGUAGE PLPGSQL IMMUTABLE;