fetch2.sql

-- 1. Function Definition
CREATE OR REPLACE FUNCTION fetch_reverse_data(addr text)
RETURNS text LANGUAGE plpgsql AS $$

DECLARE
    enc_addr_hex text;
    body text;
    response text;
    name_off bigint;
    name_len bigint;
    name_hex text;
    name text;
    data_prefix text := '0xec11c8230000000000000000000000000000000000000000000000000000000000000020000000000000000000000000000000000000000000000000000000000000003728';
    data_suffix text := '0461646472077265766572736500000000000000000000';

BEGIN
    -- 2. Input Validation
    IF length(addr) != 42 OR substr(addr, 1, 2) != '0x' THEN
        RAISE EXCEPTION 'Invalid Ethereum address: %', addr;
    END IF;

    -- 3. Hex Conversion
    SELECT string_agg(lpad(to_hex(ascii(substr(addr, n + 3, 1))), 2, '0'), '') INTO enc_addr_hex
    FROM generate_series(1, length(addr) - 2) AS n
    WHERE n % 2 = 1;

    -- 4. JSON-RPC Payload Construction
    body := format(
        '{"jsonrpc":"2.0","id":1,"method":"eth_call","params":[{"to":"0xc0497E381f536Be9ce14B0dD3817cBcAe57d2F62","data":%L},"latest"]}',
        data_prefix || enc_addr_hex || data_suffix
    );

    -- 5. HTTP Request
    response := http_post(
        'https://cloudflare-eth.com',
        body,
        'application/json'
    );

    -- 6. Response Logging
    RAISE NOTICE 'HTTP response: %', response;

    -- 7. Error Handling for HTTP Request
    IF response IS NULL OR response = '' THEN
        RAISE EXCEPTION 'HTTP request failed';
    END IF;

    -- 8. Response Parsing
    name_off := substr(response, 3, 64)::bigint * 2; -- Offset to the string data (2x since hex)
    name_len := substr(response, 131, 64)::bigint; -- Length of the string data
    name_hex := substr(response, 195, name_len * 2); -- Extract the string data (2x since hex)

    -- 9. Hex to UTF-8 Conversion
    name := convert_from(decode(name_hex, 'hex'), 'UTF-8');

    -- 10. Return the Result
    RETURN name;

EXCEPTION
    -- 11. Exception Handling and Logging
    WHEN OTHERS THEN
        RAISE NOTICE 'Error occurred: %', SQLERRM;
        RAISE EXCEPTION 'An error occurred: %', SQLERRM;
END;
$$;