Thread: Blob stuff

OK maybe I'm cutting new ground and no one has or knows how to utilize blob
data.

On a remote client the proper way to insert a blob into the table is:

1. copy myblob.gif (through ftp or scp) to the server to a predefined
directory <PreDir>
2. use INSERT INTO mytable VALUES (lo_import('<PreDir>/myblob.gif'));
3. use SELECT lo_export(mycol, '<OutDir>/myblob.gif') FROM mytable WHERE
<criteria for myblob.gif>;
4. To unlink use SELECT lo_unlink(mycol) FROM mytable WHERE <criteria for
myblob.gif>;
5. copy myblob.gif (through ftp or scp) from the server back to the remote
cliet

Any comments?

There is \lo_import, \lo_export, \lo_list, and \lo_unlink which seem to
utilize a remote client to upload, but this isn't a great solution if your
not using psql.  Even if you do use psql, I'm not sure how to include the
lo_<command> into a db solution.  Any comments?

Patrick Nelson <pnelson@neatech.com> writes:
> There is \lo_import, \lo_export, \lo_list, and \lo_unlink which seem to
> utilize a remote client to upload, but this isn't a great solution if your
> not using psql.

If you're not using psql, what are you using?

psql's \lo facilities are just interfaces to a set of libpq routines,
which you can use for yourself if you're coding in C or C++.  If you're
using some other API that doesn't offer equivalent facilities, meseems
you have a gripe against that API.

            regards, tom lane

[ Re-post after re-resubscribing. Sorry if this gets through twice. I'd
have sworn on Euclid's "Elements" I was already subscribed ... ]

To mitigate both Patrick's issue and Tom's answer, it should be said
that the current doc is quite light on blob use and issues. Some
pointers to external docs might be welcome ...

(BTW, the same could be said of a lot of non-core stuff (e. g. using
Python as a procedural language, which uses a module alluded to but
having no documentation I could lay my yeyes on ... The "Right Thing" to
do in such an odccurence might be the way the Python client interface
does : it describes the (now obsolete) pypgsql module, but points to the
  correct (meta-)reference to DB API  2.0).


Tom Lane wrote:
> Patrick Nelson <pnelson@neatech.com> writes:
>
>>There is \lo_import, \lo_export, \lo_list, and \lo_unlink which seem to
>>utilize a remote client to upload, but this isn't a great solution if your
>>not using psql.
>
>
> If you're not using psql, what are you using?
>
> psql's \lo facilities are just interfaces to a set of libpq routines,
> which you can use for yourself if you're coding in C or C++.  If you're
> using some other API that doesn't offer equivalent facilities, meseems
> you have a gripe against that API.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Tom Lane wrote:
----------------->>>>
Patrick Nelson <pnelson@neatech.com> writes:
> There is \lo_import, \lo_export, \lo_list, and \lo_unlink which seem to
> utilize a remote client to upload, but this isn't a great solution if your
> not using psql.

If you're not using psql, what are you using?

psql's \lo facilities are just interfaces to a set of libpq routines,
which you can use for yourself if you're coding in C or C++.  If you're
using some other API that doesn't offer equivalent facilities, meseems
you have a gripe against that API.
----------------->>>>

I'm mainly using the pgtcl.  Your not answering my questions.  I was saying
that what would be the process for using psql's \lo?  I've tried to
understanding by trial and error but I can not figure out a useful process.

I really want to use the lo in a select and insert statements.  But again I
can not figure out a process that works from a remote client.

Patrick Nelson <pnelson@neatech.com> writes:
> Tom Lane wrote:
> If you're not using psql, what are you using?

> I'm mainly using the pgtcl.

In that case you ignore psql's facilities and instead read about
libpgtcl's facilities for manipulating large objects, for example
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/pgtcl-pgloimport.html
and surrounding pages.

It looks like those docs could use some work :-( --- I've never looked
at them closely before, and they seem to contain some lies.  For
example, pg_lo_import should return the OID of the created object,
and does according to the code ... but the man page doesn't say anything
about it.

            regards, tom lane