PostgreSQL Blockchain Extension
WARNING: This is work-in-progress, use at your own risk!
This is a C language Postgres extension that provides Bitcoin blockchain functionality.
CREATE EXTENSION pg_blkchain;
SELECT op_sym, encode(data, 'escape')
FROM parse_script(E'\\x04ffff001d0104455468652054696d65732030332f4a616e2f32'::bytea ||
E'\\x303039204368616e63656c6c6f72206f6e206272696e6b206f66'::bytea ||
E'\\x207365636f6e64206261696c6f757420666f722062616e6b73'::bytea);
op_sym | encode
-------------+-----------------------------------------------------------------------
OP_PUSHDATA | \377\377\000\x1D
OP_PUSHDATA | \x04
OP_PUSHDATA | The Times 03/Jan/2009 Chancellor on brink of second bailout for banks
(3 rows)
or
Assuming you have a table with a BYTEA
column named tx
,
which contains transactions, you can do stuff like:
-- Note: this requires the pgcrypto extension for digest().
SELECT n_in, verify_sig(tx, ptx, n_in)
FROM (
SELECT (vin).n n_in, p.tx ptx, x.tx tx
FROM (
SELECT get_vin(tx) vin, tx
FROM rtxs
WHERE id = 37898
) x
JOIN rtxs p
ON (vin).prevout_hash = digest(digest(p.tx, 'sha256'), 'sha256')
) x;
n_in | verify_sig
------+------------
0 | t
1 | t
or
SELECT parse_script((get_vout(tx)).scriptpubkey) FROM rtxs WHERE id = 37898;
parse_script
----------------------------------------------------------------
(OP_DUP,118,)
(OP_HASH160,169,)
(OP_PUSHDATA,20,"\\x32b0f5cad60641be97317b3f013ce53f60893448")
(OP_EQUALVERIFY,136,)
(OP_CHECKSIG,172,)
(5 rows)
-- Note: this will take a while to run!
SELECT (parse_script((get_vout(tx)).scriptpubkey)).op_sym, count(1)
FROM rtxs
GROUP BY op_sym
ORDER BY count(1) DESC LIMIT 10;
op_sym | count
------------------------+-----------
OP_PUSHDATA | 678204416
OP_HASH160 | 672704434
OP_CHECKSIG | 598508189
OP_EQUALVERIFY | 597189173
OP_DUP | 597189166
OP_EQUAL | 75515405
OP_RETURN | 3017195
OP_CHECKMULTISIG | 574881
OP_TRUE | 572552
OP_9 | 2635
More details to follow. This blog post has some more info.
If you find this interesting, comment here in an issue or on twitter @humblehack, whatever. Also if you'd like to help.
This extension requires github.com/libbitc/libbitc, which is a fork of picocoin with SegWit support and other improvements.
Building and installing libbitc is up to the reader, the following worked on my Debian system perfectly:
# build and install .deb packages
git clone https://github.com/grisha/libbitc.git
cd libbitc
git submodule update --init --recursive
./autogen.sh
./configure
make pgk-deb
sudo dpkg -i ./libbitc*deb
Once you have libbitc installed, you should be able to just
make
sudo make install
This was developed and tested only on PG 9.6.