Обсуждение: Plperl return_next and bytea binary data?
Hi, I'm trying to generate graphics dynamically from a plperl function, like this: ------------------------------------ CREATE OR REPLACE FUNCTION test_gd ( IN a int4, IN b int4, OUT val int4, OUT image bytea ) RETURNS SETOF record AS $$ use GD; $im = new GD::Image(100,100); $white = $im->colorAllocate(255,255,255); $black = $im->colorAllocate(0,0,0); $im->transparent($white); $im->interlaced('false'); $im->line(0,0,150,150,gdBrushed); $im->rectangle(10,10,89,89,$black); $im->arc(50,50,95,75,0,360,$black); $im->fill(50,50,$black); return_next ( { val => $_[0] * $_[1], image => $im->gif() } ); return undef; $$ LANGUAGE 'plperlu'; ------------------------------------ The image, in hexadecimal, is: ------------------------------------ 00000000:47 49 46 38 39 61 64 00 64 00 80 00 00 ff ff ff GIF89ad.d.€..ÿÿÿ 00000010:00 00 00 21 f9 04 01 00 00 00 00 2c 00 00 00 00 ...!ù......,.... 00000020:64 00 64 00 00 02 fe 84 8f a9 cb ed 0f a3 9c b4 d.d...þ„©Ëí.£œ´ 00000030:da 8b b3 de bc fb 0f 86 e2 48 96 e6 89 a6 ea ca Ú‹³Þ¼û.†âH–扦êÊ 00000040:b6 ee 0b 63 c1 4c d7 f6 8d e7 fa ce f7 b3 1c 2b ¶î.cÁL×öçúÎ÷³.+ 00000050:05 80 c1 d1 f0 72 14 f9 7e 4a 22 67 e9 eb 24 2b .€ÁÑðr.ù~J"géë$+ 00000060:53 24 f4 5a a5 3a a9 d8 2e 0d b9 8d 78 c7 37 4a S$ôZ¥:©Ø..¹xÇ7J 00000070:56 72 66 90 d7 65 71 58 cd 8e db 20 69 3a 5d 8e Vrf×eqXÍŽÛ i:]Ž 00000080:ff 3a ea 0f 7e fe 5f d3 c0 b7 07 07 68 c8 94 30 ÿ:ê.~þ_ÓÀ·..hÈ”0 00000090:28 b8 70 d8 88 78 a0 a8 96 e8 48 39 15 c9 88 50 (¸p؈x ¨–èH9.ɈP 000000a0:a9 09 e9 a4 e9 69 70 a9 70 e4 49 0a 10 9a 68 4a ©.é¤éip©päI..šhJ 000000b0:aa 0a a4 ba 6a d5 5a fa 0a fb c9 35 fb 79 9a 6a ª.¤ºjÕZú.ûÉ5ûyšj 000000c0:bb 79 3a a4 bb 69 8a 06 ea 4b c9 d9 37 3a ec 08 »y:¤»iŠ.êKÉÙ7:ì. 000000d0:39 98 84 9c 5c bc 28 dc 6c 98 49 a8 2c 0d 88 2a 9˜„œ\¼(Ül˜I¨,.ˆ* 000000e0:69 7d 9d 97 2d 6a c9 fd 37 89 99 19 de ed 4d 5d i}—-jÉý7‰™.ÞíM] 000000f0:6e 2e 27 4a be bd be d6 3e ae 0e 1f 3f ff fc 5e n.'J¾½¾Ö>®..?ÿü^ 00000100:ef e5 0e fa ad 4f c6 0f d8 a4 7f 63 02 66 21 08 ïå.úOÆ.ؤc.f!. 00000110:50 1e be 68 08 b1 18 f4 d7 f0 ca c3 81 11 a1 4c P.¾h.±.ô×ðÊÃ.¡L 00000120:a4 57 31 8a c2 b1 7e 14 33 f6 30 58 c5 a3 c5 7b ¤W1ŠÂ±~.3ö0XÅ£Å{ 00000130:0b 45 2e d9 58 d2 e4 47 74 29 55 ee 40 29 0c a3 .E.ÙXÒäGt)Uî@).£ 00000140:cb 36 ca b4 31 9c 49 93 23 3f 9c 3a 58 7a e3 99 Ë6Ê´1œI“#?œ:Xzã™ 00000150:23 1d b4 5c 40 03 2d fc 76 b3 28 33 45 c7 94 ea #.´\@.-üv³(3EÇ”ê 00000160:11 68 c7 a9 51 5e 52 bf e0 aa 6a d5 02 d6 47 13 .hÇ©Q^R¿àªjÕ.ÖG. 00000170:b6 b2 92 fa d5 29 11 a5 50 b5 24 35 a9 d3 ec d9 ¶²’úÕ).¥Pµ$5©ÓìÙ 00000180:8c 47 83 c9 ac 28 54 ed db 7f 30 dd 42 24 18 b0 ŒGƒÉ¬(TíÛ0ÝB$.° 00000190:eb 1e ba 36 e5 16 0a d7 e7 0d 23 c0 81 c1 a0 69 ë.º6å..×ç.#ÀÁ i 000001a0:66 d7 ef 1d 5b 66 04 8b 89 a5 38 b1 2c 6c 19 70 f×ï.[f.‹‰¥8±,l.p 000001b0:b5 9d 6c 6f 83 e5 b4 20 46 86 d8 5c b6 c8 07 d0 µloƒå´ F†Ø\¶È.Ð 000001c0:5e 45 9b 3e 8d 3a b5 ea d5 ac 5b bb 7e 0d 3b b6 ^E›>:µêÕ¬[»~.;¶ 000001d0:ec d9 b4 6b bb 2e 00 00 3b ìÙ´k»...; ------------------------------------ By sniffing the network, I could see that the image is not transferred completely to the client. Only the first part is being transferred: ------------------------------------ 00000000:47 49 46 38 39 61 64 GIF89ad ------------------------------------ The problem seems to come from the return_next, in conjunction with binary data: ------------------------------------ return_next ( { val => $_[0] * $_[1], image => $im->gif() } ); ------------------------------------ Doe anybody know how to solve that problem? When saving data to the hard disk, with GD and perl, examples show that you haveto force the stream in binary mode, like this: ------------------------------------ open (MYOUTFILE, ">/tmp/outfile.gif") || die 'Cannot open output file'; binmode MYOUTFILE; print MYOUTFILE $im->gif(); close MYOUTFILE; ------------------------------------ I tried, and it works... Is there a trick in order to force binary mode with return_next too? Thanks for your help! ---------------------------------- Philippe Lang, Ing. Dipl. EPFL Attik System rte de la Fonderie 2 1700 Fribourg Switzerland http://www.attiksystem.ch Tel: +41 (26) 422 13 75 Fax: +41 (26) 422 13 76
Вложения
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... -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
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... Philippe
Вложения
pgsql-general-owner@postgresql.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
Вложения
Philippe Lang wrote: > Juste one more question: what is the maximum size of a TEXT column in > PG 8.1.4? A handful of bytes less than 1 GB. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: > Philippe Lang wrote: > >> Juste one more question: what is the maximum size of a TEXT column in >> PG 8.1.4? > > A handful of bytes less than 1 GB. It should be ok then... :) --------------- Philippe Lang Attik System