-
Notifications
You must be signed in to change notification settings - Fork 174
/
block_info.sql
56 lines (53 loc) · 1.4 KB
/
block_info.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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
DROP FUNCTION IF EXISTS grest.block_info (_block_hash text);
CREATE FUNCTION grest.block_info (_block_hash text)
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,
CHILD_HASH text)
LANGUAGE PLPGSQL
AS $$
BEGIN
RETURN QUERY
SELECT
_block_hash 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,
(
SELECT
ENCODE(tB.HASH::bytea, 'hex')
FROM
block tB
WHERE
id = b.id - 1) AS PARENT_HASH,
(
SELECT
ENCODE(tB.HASH::bytea, 'hex')
FROM
block tB
WHERE
id = b.id + 1) AS CHILD_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
WHERE
DECODE(_block_hash, 'hex') = B.HASH;
END;
$$;
COMMENT ON FUNCTION grest.block_info IS 'Get detailed information about a specific block';