PostgreSQL：从OID到Bytea [英] PostgreSQL: from OID to Bytea

问题描述

我们已决定从PostgreSQL 9.0数据库中的 OID 移出，而使用 bytea 列。我正在尝试将数据从一列复制到另一列，但是我找不到正确的查询。这是我最近得到的：

We have decided to move from OIDs in our PostgreSQL 9.0 database and use bytea columns instead. I'm trying to copy the data from one column to the other, but I can't figure out the right query. This is the closest I've gotten to:

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

这给了我以下错误消息：

And that gives me the following error message:

ERROR:  column "pkcs_as_bytea" is of type bytea but expression is of type bytea[]

那么正确的查询是什么？

What would be the right query then?

推荐答案

下面是一个执行魔术的存储过程：

Here is a stored procedure that does the magic:

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;

这篇关于PostgreSQL：从OID到Bytea的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！