Re: Resizing images contained in oid fields - Mailing list pgsql-admin

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.