Re: Plperl return_next and bytea binary data?

pgsql-general-owner(at)postgresql(dot)org wrote:

> Martijn van Oosterhout wrote:
>
>> On Mon, Jul 24, 2006 at 11:43:39AM +0200, Philippe Lang wrote:
>>> The problem seems to come from the return_next, in conjunction with
>>> binary data:
>>>
>>> ------------------------------------
>>> return_next
>>> (
>>> {
>>> val => $_[0] * $_[1],
>>> image => $im->gif()
>>> }
>>> );
>>> ------------------------------------
>>
>> I don't know exact how pl/perl works, but given that it has no idea
>> what type the data is, chances are it's passing it through
>> cstring-to-text conversion. You probably want to force it to return
>> bytea or some such (without going through cstring-to-bytea conversion
>> hopefully). I don't see a way to do it in the documentation though...
>
> Casting $im->gif() to bytea with $im->gif()::bytea does not
> help. It even makes things slightly worse: the image returned
> (ethereal sniff) is completely empty, where before it was
> filled with the first characters "GIF89ad" of the image.
>
> Still searching...

I finally found a solution to my problem by using a base64 encoded gif image, in conjunction with a text column:

----------------------------------
CREATE OR REPLACE FUNCTION test_gd
(
IN a int4,
IN b int4,

OUT val int4,
OUT image text
)
RETURNS SETOF record
AS

$$
use GD;
use MIME::Base64::Perl;

$im = new GD::Image(100,100);
$white = $im->colorAllocate(255,255,255);
$black = $im->colorAllocate(0,0,0);
$red = $im->colorAllocate(255,0,0);
$im->transparent($white);
$im->rectangle(0,0,89,89,$black);
$im->arc(50,30,95,75,0,360,$black);
$im->fill(50,50,$red);

$image = $im->gif();

return_next
(
{
val => $_[0] * $_[1],
image => encode_base64($image, '')
}
);

return undef;
$$

LANGUAGE 'plperlu';
----------------------------------

For the small technical drawings this system is suppose to handle, this is just fine.

Juste one more question: what is the maximum size of a TEXT column in PG 8.1.4?

---------------
Philippe Lang
Attik System