PostgreSQL:从OID到Bytea
问题描述:
我们决定从我们的PostgreSQL 9.0数据库中的OID
中移出,并使用bytea
列代替。我试图将数据从一列复制到另一列,但我无法弄清楚正确的查询。这是我已经得到了最靠近:PostgreSQL:从OID到Bytea
update user as thistable set pkcs_as_bytea = (select array_agg(mylargeobject.data) from
(select * from pg_largeobject where loid = thistable.pkcs12_as_oid order by pageno) as mylargeobject) where thistable.pkcs12 is not null
这给了我以下错误消息:
ERROR: column "pkcs_as_bytea" is of type bytea but expression is of type bytea[]
什么是正确的查询呢?
答
下面是一个存储过程,确实神奇:
CREATE OR REPLACE FUNCTION merge_oid(val oid)
returns bytea as $$
declare merged bytea;
declare arr bytea;
BEGIN
FOR arr IN SELECT data from pg_largeobject WHERE loid = val ORDER BY pageno LOOP
IF merged IS NULL THEN
merged := arr;
ELSE
merged := merged || arr;
END IF;
END LOOP;
RETURN merged;
END
$$ LANGUAGE plpgsql;
答
对于文本数组,您需要类似array_to_string(anyarray, text)
的东西,但在这种情况下,需要使用array_to_bytea(largeobjectarray)
来连接所有节。你必须自己创建这个函数,或者在应用程序逻辑中处理这个函数。
答
不需要自定义函数的另一种方法是使用loread(lo_open(...))
组合,如:
UPDATE user SET pkcs_as_bytea = loread(lo_open(pkcs12_as_oid, 262144), 1000000) WHERE thistable.pkcs12 IS NOT NULL
有一个这个代码的问题,loread
函数需要读取的最大字节数(我在上面使用的1000000
参数)作为第二个参数,所以如果数据很大,你应该在这里使用一个非常大的数字。否则,内容将在这么多字节之后被剪裁,并且您不会将所有数据返回到bytea
字段。
如果你想从OID转换为文本字段,你也应该使用转换功能,如:
UPDATE user SET pkcs_as_text = convert_from(loread(lo_open(pkcs12_as_oid, 262144), 1000000), 'UTF8')
(262144
是开放模式,40000
在六,这意味着一个标志“open read-only”)
`array_agg()`返回一个数组,所以错误信息是有道理的。你为什么认为你需要在那里聚合字节? – 2011-02-18 12:42:23
我需要将不同oid行上的blob合并到一个列中。有没有更好的方法来做到这一点? – malaverdiere 2011-02-21 04:37:49