Get TEXT value of a CLOB OID in Postgresql

Question

I have a database table that looks like:

create table answers(
   id int not null,
   question_id int not null,
   answer text null
)

This table was originally build by Hibernate using the @Lob attribute for the "answer" column. I did not realize it at the time, but when setup that way, Hibernate stores an OID in the column instead of the actual text. Everything works fine when I use Hibernate to retrieve the values since it automatically converts the OID to the CLOB string, however it is becoming a performance problem and I'd like to get rid of the OID.

select * from answers
ID     QUESTION_ID     ANSWER
===============================
1       123             55123
2       234             51614
3       345             56127
should be
ID     QUESTION_ID     ANSWER
===============================
1       123             Male
2       234             203-555-1212
3       345             555 Main St. New York, NY

My desire is to add an extra column to the table "ANSWER_VALUE TEXT" and do something like below to get the actual value into the table, then change Hibernate around to not use the @Lob designator

update answers set ANSWER_VALUE= getValueFromOID(ANSWER)

Does that "getValueFromOID" function exist? If not, could I get some pointers on how to create one or at least how to fetch the actual value of an OID?

Thanks

Answer 1

Thanks goes to a_horse_with_no_name. Solution is:

update answers set answer_value = lo_get(cast(value as bigint))

Note - the lo_get function appears to be present in Postgres 9.4 or higher. For earlier versions, I don't see a way of doing this directly. I'm currently running 9.0, but this just accelerated my upgrade plans.

Answer 2

Query : SELECT encode(lo_get([OID], [offset], [length]), 'escape');

You can get the actual data present at particular OID and then execute update query. e.g.,

temp_table => SELECT encode(lo_get(238026, 0, 10), 'escape');
 encode 
--------
 world
(1 row)