Re: pg_dump without blobs

Joshua D. Drake skrev:
> Vivek Khera wrote:
>>
>> On Jul 16, 2007, at 9:26 AM, Francisco Reyes wrote:
>>
>>> I guess the next question is 'what does postgresql considers a blob'?
>>> bytea fields? How about a large text with megabytes worth of data?
>>
>> bytea and text fields are NOT blobs.  they are what you access via the
>> 'large object' functions.
>
> To follow up on this.
>
> In oracle large text CLOB and binary objects BLOB are synonomous (I
> believe) with PostgreSQL TEXT and BYTEA.

I don't have experience with Oracle versions >8, but back then this was
not the case. Back then, Oracles LOBs had a special syntax for access,
quite similar to the one documented here:

http://www.postgresql.org/docs/8.2/interactive/largeobjects.html

> PostgreSQL also supports a non standard, and frankly better
> implementation called lo for binary data, which also uses BYTEA data but
> breaks it up to make it more efficient per row.

What I would really like would be for the two models to be merged - a LO
should be a datatype in its own right, having all the same functionality
as BYTEA, plus the additional interface. As you say, this is an
additional implementation, and the programmer shouldn't need additional
syntax unless it needs the additional functionality.

So in my dream world, you could do:

CREATE TABLE image (
    name            text,
    raster          LOB
);

INSERT INTO image (name, raster)
    VALUES ('beautiful image', lo_import('/tmp/image.gif'));

INSERT INTO image (name, raster)
    VALUES ('beautiful image 2', '
 _
/ \
|O|
\_/
ASCII ART RULEZ'));

SELECT name, raster FROM image
    WHERE name = 'beautiful image';

SELECT name, lo_export(raster, '/tmp/ascii.txt') FROM image
    WHERE name = 'beautiful image 2';

I realize that there may be all kinds of implementation problems with
this - but this is how it 'ought' to work, IMO.

Nis