Thread: Seek within Large Object, within PL/* function?
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?
Thanks,
Adam
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>: > > > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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