Fetching Binary Data

[kjurka]

The fact that http_get returns varchar prevents this from being used to return binary data. I was specifically trying to fetch some images from a webserver.

[pramsey]

Hm, the data should be copied into the memory segment without alteration. Is it possible to cast it into a bytea and use it?

[kjurka]

The problem is with bytes with a value of zero. There is no length field for varchar, it's null terminated. So your data will be truncated at the first zero.

[pramsey]

I've updated the documentation, and ensured the core code treats all data in a "binary clean" way, so you just need to use the right magic function to convert the varchar content into a bytea: use the textsend(text) function.

    WITH 
      http AS ( 
        SELECT * FROM http_get('http://localhost/PoweredByMacOSXLarge.gif') 
      ), 
      headers AS (
        SELECT (unnest(headers)).* FROM http
      ) 
    SELECT 
      http.content_type,
      length(textsend(http.content)) AS length_binary, 
      headers.value AS length_headers 
    FROM http, headers 
    WHERE field = 'Content-Length';

And for my example, I get this.

     content_type | length_binary | length_headers 
    --------------+---------------+----------------
     image/gif    |         31958 | 31958

You can see the underlying data is still there, and the length retrieved (as noted in the headers) is the same as the length in the underlying object (as measured after casting to bytea with textsend(text)

[mclei-asw]

This recommendation does not work, when client charset is different from database charset. For example, when my database charset is UTF-8 and client charset is WINDOWS-1250, then I get error about conversion:
ERROR: invalid byte sequence for encoding "UTF8": 0xe2 0xe3 0xcf

I have solved it be force defining SET client_encoding = 'UTF8' for the function, which calls the http_post:

create or replace function test (
  in URL text,
  in RequestData text,
  in ContentType text
)  returns test_rs
language plpgsql SET client_encoding = 'UTF8' as $$
declare
  l_Result                 test_rs;
begin
  select status, content_type, textsend(content) into l_Result.STATUS, l_Result.CONTENTTYPE, l_Result.CONTENT
    from public.http_post(URL, RequestData, ContentType);
  return l_Result;
end; $$;