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?

回答1:

Another way which doesn't require a custom function is to use the loread(lo_open(...)) combination, like:

UPDATE user SET pkcs_as_bytea = loread(lo_open(pkcs12_as_oid, 262144), 1000000) WHERE thistable.pkcs12 IS NOT NULL

There is a problem with this code, the loread function requires as the second parameter the maximum number of bytes to read (the 1000000 parameter I used above), so you should use a really big number here if your data is big. Otherwise, the content will be trimmed after this many bytes, and you won't get all the data back into the bytea field.

If you want to convert from OID to a text field, you should also use a conversion function, as in:

UPDATE user SET pkcs_as_text = convert_from(loread(lo_open(pkcs12_as_oid, 262144), 1000000), 'UTF8')

(262144 is a flag for the open mode, 40000 in hexa, which means "open read-only")

回答2:

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;

回答3:

You need something like array_to_string(anyarray, text) for text arrays, but in this case an array_to_bytea(largeobjectarray) to concat all sections. You have to create this function yourself, or handle this in application logic.

回答4:

well, i did something like this. I have attachment table and content column with data in oid type. I migrated with four actions:

ALTER TABLE attachment add column content_bytea bytea
UPDATE attachment SET content_bytea = lo_get(content)
ALTER TABLE attachment drop column content
ALTER TABLE attachment rename column content_bytea to content