CODEWITHSUNDEEP
pythonpostgresqlencodingplpgsqlbytea
CitizenFish
CitizenFish

Reputation: 391

Postgres BYTEA encoding and JSONB output

I have built an API on top of Postgres that is routed through a PLPGSQL function that always returns JSONB. eg:-

SELECT api('{"method", "foo"}'::JSONB)

Returns:-

{
 "response_code" : 200,
 "data" : "...."
}

I want to use this gateway to render vector tiles which are in PBF format. I have a function create_vector_tile(x,y,z) which returns BYTEA and this works perfectly if I connect a python script to it directly:-

query = DATABASE_CONNECTION.execute(create_vector_tile(%,%,%), [x,y,z])
ret = query.fetchone()[0]
self.send_response(200)
self.send_header("Access-Control-Allow-Origin", "*")
self.send_header("Content-type", "application/vnd.mapbox-vector-tile")
self.end_headers()
self.wfile.write(ret)

How do I decode this bytea if I return it via my API eg:-

RETURN json_build_object('response_code', 200, 'data', create_vector_tile(x,y,z))

Postgres automatically encodes the bytea as a string in the JSON object but I don't know how to decode this in python to get the same result as the direct call. I need to go via my API as it does authentication and logging.

Upvotes: 0

Views: 817

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55640

Postgresql's BYTEA is a hex string preceded by \x. Assuming it arrives with the leading backslash properly escaped it can be converted to bytes using bytes.fromhex:

>>> bytea = '\\x1aa7360a0764656661756c7412820412e10300000101020203030404050506060707080809090a030b0a0c0b0d0c0e0d0f'
>>> bs = bytes.fromhex(bytea[2:])
>>> bs
b'\x1a\xa76\n\x07default\x12\x82\x04\x12\xe1\x03\x00\x00\x01\x01\x02\x02\x03\x03\x04\x04\x05\x05\x06\x06\x07\x07\x08\x08\t\t\n\x03\x0b\n\x0c\x0b\r\x0c\x0e\r\x0f'

If the backslash is not escaped, this will work:

>>> bytea = '\x1aa7360a0764656661756c7412820412e10300000101020203030404050506060707080809090a030b0a0c0b0d0c0e0d0f'
>>> escaped = bytea.encode('unicode-escape').decode('ascii')
>>> bytes.fromhex(escaped[2:])
b'\x1a\xa76\n\x07default\x12\x82\x04\x12\xe1\x03\x00\x00\x01\x01\x02\x02\x03\x03\x04\x04\x05\x05\x06\x06\x07\x07\x08\x08\t\t\n\x03\x0b\n\x0c\x0b\r\x0c\x0e\r\x0f'

Upvotes: 1

Related Questions