/
blocks.sql
42 lines (39 loc) · 990 Bytes
/
blocks.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
DROP FUNCTION IF EXISTS grest.blocks ();
CREATE FUNCTION grest.blocks ()
RETURNS TABLE (
HASH text,
EPOCH uinteger,
ABS_SLOT uinteger,
EPOCH_SLOT uinteger,
HEIGHT uinteger,
BLOCK_TIME timestamp,
TX_COUNT bigint,
VRF_KEY varchar,
OP_CERT_COUNTER word63type,
POOL varchar,
PARENT_HASH text)
LANGUAGE PLPGSQL
AS $$
BEGIN
RETURN QUERY
SELECT
ENCODE(B.HASH::bytea, 'hex') AS HASH,
b.EPOCH_NO AS EPOCH,
b.SLOT_NO AS ABS_SLOT,
b.EPOCH_SLOT_NO AS EPOCH_SLOT,
b.BLOCK_NO AS HEIGHT,
b.TIME AS BLOCK_TIME,
b.TX_COUNT,
b.VRF_KEY,
b.OP_CERT_COUNTER,
ph.VIEW AS POOL,
LAG(ENCODE(b.HASH::bytea, 'hex')) OVER (ORDER BY b.ID) AS PARENT_HASH
FROM
BLOCK B
LEFT JOIN SLOT_LEADER SL ON SL.ID = B.SLOT_LEADER_ID
LEFT JOIN POOL_HASH PH ON PH.ID = SL.POOL_HASH_ID
ORDER BY
B.ID DESC;
END;
$$;
COMMENT ON FUNCTION grest.blocks IS 'Get detailed information about all blocks (paginated - latest first)';