How to return a binary string (bytea) in a PL / Python PostgreSQL routine?

Question

I am currently trying to write a procedure in PL / Python to perform some data conversion, and then return the result as bytea . (this is pretty ugly, actually: sorting data in OCaml! Horrible in Python and OCaml right away, should I get a medal?)

Here's what it looks like:

 CREATE OR REPLACE FUNCTION ml_marshal(data varchar) RETURNS bytea as $$ import tempfile, os fn = tempfile.mktemp() f = open(fn, 'w') dest_fn = tempfile.mktemp() f.write("let outch = open_out_bin \"" + dest_fn + "\" in " + "Marshal.to_channel outch (" + data + ") [Marshal.No_sharing]; " + "close_out outch") f.close() os.system("ocaml " + fn) os.unlink(fn) f = open(dest_fn, 'r') res = f.read() f.close() os.unlink(dest_fn) return res $$ LANGUAGE plpythonu; 

In short, he writes out a small OCaml program in tempfile, which creates another tempfile with the data we need. Then we read this tempfile, destroy them and return the result.

Only this does not work:

 meidi=# select * from tblmodel; modelid | polies ---------+------------------ 1 | \204\225\246\276 2 | \204\225\246\276 

In each of the four bytes (should be ~ 130). If I stop it from unlocking files, it becomes obvious why; there are four non-NUL bytes followed by a pair of NULs and it seems these NULs are treated as terminators at some point by converting from Python to Postgres!

Does anyone know why this is happening, or how to stop it? Documents are not enlightening.

Thanks!

Change I found someone else with the same problem , but the solution is rather unsatisfactory.

Answer 1

This has been fixed with release 9.0. I had the same problem, so I updated it. From the release note :

 Improve bytea support in PL/Python (Caleb Welton) Bytea values passed into PL/Python are now represented as binary, rather than the PostgreSQL bytea text format. Bytea values containing null bytes are now also output properly from PL/Python. Passing of boolean, integer, and float values was also improved. 

I do not think that in previous versions of PostgreSQL there is a very elegant solution to this problem.

Answer 2

You can apply other bodge-encode code for the return value from python as base64 and use the PostgreSQL decoding function to decode it, namely: decode(ml_marshal(xxx), 'base64') .

Or upgrade to 9.0 as indicated by Adrian :)