Displaying images for SQL Reports

When creating SQL reports that need to pull images from OpenERP, simply selecting the column that holds the image is not enough. OpenERP stores images base64 encoded, so the decode function needs to be used in order to convert it to proper binary.

However, one problem is that OpenERP sometimes injects characters into the base64 encoded text. These need to be filtered out. The following example SQL shows how to do this:

select decode(replace(encode(logo,'escape')::text,E'\012',''),'base64') as logo from res_company;

This selects all the logos from all of the companies configured in an openerp database.