Seek within Large Object, within PL/* function?

Adam Seering wrote:

Hi,
I'm new here, apologies if this is the wrong list...

I'm playing around with storing custom preprocessed data structures
within Large Objects. I'd like to be able to write a custom function
that will, within a query, let me select out particular bytestrings from
the middle of a Large Object (within C, I think 'lo_lseek' and 'lo_read'
would get me what I want).

I'd like to do so from some PL/* embedded language, rather than a
compiled C module. I can't find any documentation on doing this,
though. Is it possible? Thoughts?

According to http://www.postgresql.org/docs/8.3/static/lo-funcs.html ,
the functions are server side too

postgres=# select proname from pg_proc where proname like 'lo%';
proname
-------------
lo_close
lo_creat
lo_create
lo_export
lo_import
lo_lseek
lo_open
lo_tell
lo_truncate
lo_unlink
log
log
log
loread
lower
lowrite

It's odd that loread and lowrite don't follow the naming convention with
the underscore (8.3.3 win32)?

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

Klint Gore wrote:

I'm playing around with storing custom preprocessed data
structures within Large Objects. I'd like to be able to write a
custom function that will, within a query, let me select out
particular bytestrings from the middle of a Large Object (within C, I
think 'lo_lseek' and 'lo_read' would get me what I want).

According to http://www.postgresql.org/docs/8.3/static/lo-funcs.html ,
the functions are server side too

Thanks for the link; still trying to figure out how to use these
functions, though.

The link that you sent suggests that these commands are really only
useful for writing out data to server-side files, which isn't so much
what I want. What I really want is to lo_open a file, then lo_lseek to
a particular address and loread a constant number of bytes (to be
returned as the query result), then lo_close the file.

I'm currently stuck on the easy part: lo_open always returns 0, which
gives me "ERROR: invalid large-object descriptor: 0" when I try to use
it with loread.

Any further thoughts?

Thanks,
Adam

Hello

I am not sure, but you are first who try to work with LO inside PL.
Current LO interface works well for client side, and probably doesn't
work on server side via SPI interface - so I am sure, so some is
possible, but you need do some C hacking.

regards
Pavel Stehule

2008/11/15 Adam Seering <aseering@gmail.com>:

Adam Seering wrote:

Thanks for the link; still trying to figure out how to use these
functions, though.
The link that you sent suggests that these commands are really only
useful for writing out data to server-side files, which isn't so much

what I want. What I really want is to lo_open a file, then lo_lseek

to

a particular address and loread a constant number of bytes (to be
returned as the query result), then lo_close the file.

Out of curiosity, I've just quickly tried this:

CREATE OR REPLACE FUNCTION lo_part(oid,integer,integer) RETURNS bytea
AS $$
declare
fd integer;
c bytea;
i integer;
begin
fd = lo_open($1, 262144); -- INV_READ
i=lo_lseek(fd,$2,0); --arg 2 is offset
c = loread(fd, $3); -- arg 3 is size
i=lo_close(fd);
return c;
end;
$$ LANGUAGE 'plpgsql';

...and it appears to work, isn't it what you say you need?

I'm currently stuck on the easy part: lo_open always returns 0,

which

gives me "ERROR: invalid large-object descriptor: 0" when I try to

use

it with loread.

Generally it's because you've forgotten to start a transaction. lo_*
functions need to be executed inside a transaction, otherwise you get
that error (the message is not exactly helpful in this particular case,
admittedly!)

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org