[SOLVED] PostgreSQL: from OID to Bytea

PostgreSQL: from OID to 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?

Solution

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;