X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AF36953434 for ; Thu, 24 Mar 2005 08:15:08 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 21409-02 for ; Thu, 24 Mar 2005 08:15:00 +0000 (GMT) Received: from resmta03.ono.com (smtp.ono.com [62.42.230.12]) by svr1.postgresql.org (Postfix) with ESMTP id 2FB4B53428 for ; Thu, 24 Mar 2005 08:14:59 +0000 (GMT) Received: from [192.168.1.100] (84.122.179.122) by resmta03.ono.com (7.2.052) (authenticated as juanmime) id 422CFDDC0015333E; Thu, 24 Mar 2005 09:14:57 +0100 Message-ID: <4242770D.9020905@ono.com> Date: Thu, 24 Mar 2005 09:15:09 +0100 From: Juan Miguel User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.1) Gecko/20040707 X-Accept-Language: es-es, en-us, en MIME-Version: 1.0 To: Michael Fuhr Cc: pgsql-admin@postgresql.org Subject: Re: Resizing images contained in oid fields References: <422CFD5E0000A55E@resmta04.ono.com> <20050324013439.GA60339@winnie.fuhr.org> In-Reply-To: <20050324013439.GA60339@winnie.fuhr.org> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.75 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST X-Spam-Level: * X-Archive-Number: 200503/384 X-Sequence-Number: 16882 Michael Fuhr wrote: >On Mon, Mar 21, 2005 at 05:12:52PM +0100, juanmime@ono.com wrote: > > >>I have a table where an oid field is used for saving images. I'm thinking >>about getting a little snapshot of all images without downloading the full >>images. I only want to download some entire images. I'm thinking in something >>like this: >> >>create table images ( >> id serial primary key, >> title varchar not null, >> photo_id oid >>); >> >>select title, snapshot(photo_oid, 120, 120) as snap from images >> >>In this case, the snapshot function returns the resized snapshot of the original >>image. >> >> > >Why not store the resized image in another column? Retrieval would >probably be more efficient than running an algorithm over each image >every time you wanted to fetch the "snapshots" (thumbnails?). > > > >>I think that the core of function could be similar to this: >>1) Obtain the image >>2) Resize the Image to new size >>3) Return the Resized Image >> >>Do you know if there exists a function like this ("snapshot")? >> >> > >Not in the standard PostgreSQL installation. > > > >>Otherwise, What is the type returned by the function ? What suitable procedure >>language should I use ? What image library for the redimension ? >> >> > >I'd probably make the return type bytea. I'm sure several of the >procedural languages (PL/Perl, PL/Tcl, PL/Python) have modules that >interface to graphics libraries that can resize images; check their >respective web sites (CPAN for Perl, etc.). If I had to write this >function I'd probably use C and ImageMagick, but I'd be more likely >to generate the thumbnails on the client side and store them in >another column. > > > Tanks Michael, This example is a simplification of the problem. I know that a good solution could be adding a column where storing the resized image. And I think that a solution, could be creating a trigger, lauched before insert or update, that autogenerate the thumbnail. The new function could looks like this: create or replace function createthumbnail (oid, integer, integer) returns oid as ' ......'; The params will be (oid of the big image, width, height) and returns the oid of the resized image. and after. I will create the trigger that uses this function. It is the best solution that I'm found.But there are two problems: *) What happend if the size will be dinamically selected by the client application ? *) We need two oids per image (big and resized). Anyway, in my scene, the thumbnails dimensions will be constant, and the DB only will store a few hundred of images. I think to program the image using the gd_lib library in "c". But I think that this library needs the image file for reading, or for writting. Because I'm using oids, I still don't know how to stablish this match...Phereaps I need a library that reads/writes from/to a buffer instead from/to a file. Thanks Michael... If you are interented, I will send you the function/s when I will finish.