Convert lo_get() result to text in Postgresql?

Question

I was using lo_import() + lo_getfunctions to read file content in my server, but the result is not data we can read

code:

SELECT lo_import($$/home/x/web/a.php$$);

this will give an oid = 12312

then i can get file contet by using this oid:

SELECT lo_get(12312);

but the result was: " i hide the rest of code, because afraid of sensitive data leaked "

\x3c3f7068700d0a66756e6374696f6e206563686f5f6d6f6e7468202824....

i read here Importing bytea data into PostgreSQL by using COPY FROM stdin this maybe Base64 or Hex so i tried

https://www.convertstring.com/EncodeDecode/HexDecode
https://www.base64decode.org/

but it gives me that the format is invalid .

also i checked the official document :

https://www.postgresql.org/docs/10/lo-funcs.html

but its not clear what format is it

i also have checked this : https://dba.stackexchange.com/questions/53309/using-postgresql-8-4-how-to-convert-bytea-to-text-value-in-postgres

but its far different from my question.

Basion

any solutions ? – Basion

Answer 1

just commenting because I did something similar and i always come back to this post somehow. I don't know if that is what you are searching for, but this worked for me,

UPDATE yourtable SET newColumnWithText= convert_from(lo_get(yourtable.oldColumnWithOids)::bytea, 'UTF8') where yourtable.oldColumnWithOids is not null;

However, we wanted as an organization to go away from clobs. So maybe you can make a test_table to read and after it, you can delete it.
If it's just the reading of values, for me intellij was able to change the format to readable, so maybe your IDE settings support it already

Calicut