BLOB conversion

thecrgrt wrote:

How can i convert BLOB (sub_type text) to CHAR/VARCHAR using SQL command in PSQL?

Helen Borrie answers:

Without a UDF, you can't convert a blob to a string. However, you can read from a text blob using SUBSTRING() and return a character type.

create procedure...
as
declare variable astring varchar(32767) character set BLAH;
....
begin
   select substring(atextblob from 1 for 32767) from atable
   where .........
   into :astring;
   ...
end

Watch out for overflows. The maximum length of a varchar is 32767 BYTES (not characters). To avoid overflows and transliteration errors you'll need to be concerned about character set and also whether the text is in a multi-byte character set.