如何在 PL/Python PostgreSQL 例程中返回二进制字符串 (bytea)？

发布于 2024-10-14 04:20:14 字数 1325 浏览 8 评论 0原文

我目前正在尝试在 PL/Python 中编写一个过程来执行一些数据的转换，然后将结果作为 bytea 返回。（实际上，它非常丑陋：在 OCaml 中编组数据！同时在 Python 和 OCaml 中丑陋；我应该获得奖牌吗？）

它看起来像这样：

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;

简而言之，它将一个小的 OCaml 程序写入一个临时文件，该临时文件创建另一个tempfile 包含我们想要的数据。然后我们读入该临时文件，销毁它们，然后返回结果。

只是它不太有效：

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

每个字节有四个字节（应该有〜130）。如果我阻止它取消文件链接，原因就很明显了；有四个非 NUL 字节，后跟几个 NUL，并且似乎这些 NUL 在从 Python 到 Postgres 的转换的某个阶段被视为终止符！

有谁知道为什么会发生这种情况，或者如何阻止它？文档没有启发性。

谢谢！

编辑：我发现其他人同样的问题，但解决方案相当不令人满意。

原文

I'm currently trying to write a procedure in PL/Python to perform a conversion of some data, then return the result as a bytea. (it's quite ugly, actually: marshalling the data in OCaml! Ugly in Python and OCaml at once; 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, it writes out a small OCaml program to a tempfile which creates another tempfile with the data we want. We then read that tempfile in, destroy them both, and return the result.

Only it doesn't quite work:

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

There are four bytes in each (there should be ~130). If I stop it unlinking the files, it becomes obvious why; there are four non-NUL bytes, followed by a couple of NULs, and it appears those NULs are treated as terminators at some stage by the conversion from Python to Postgres!

Does anyone know why this happens, or how to stop it? Docs are not enlightening.

Thanks!

Edit: I found someone else with the same problem, but the solution is fairly unsatisfactory.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不知所踪 2024-10-21 04:20:14

9.0 版已修复此问题。我遇到了同样的问题，所以我升级了。从发行说明：

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.

我不认为有对于以前的 PostgreSQL 版本中的这个问题，有一个非常优雅的解决方案。

This was fixed with release 9.0. I had the same problem so I upgraded. From the release notes:

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 there is a very elegant solution for this problem in previous PostgreSQL versions.

回复收藏 0 原文