Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Not that I've used large objects, but looking at the docs: <a href="http://www.postgresql.org/docs/current/interactive/lo-interfaces.html#LO-TELL">http://www.postgresql.org/docs/current/interactive/lo-interfaces.html#LO-TELL</a></p> <p>I think you have to use the same technique as some file system APIs require: seek to the end, then tell the position. PostgreSQL has SQL functions that appear to wrap the internal C functions. I couldn't find much documentation, but this worked:</p> <pre><code>CREATE OR REPLACE FUNCTION get_lo_size(oid) RETURNS bigint VOLATILE STRICT LANGUAGE 'plpgsql' AS $$ DECLARE fd integer; sz bigint; BEGIN -- Open the LO; N.B. it needs to be in a transaction otherwise it will close immediately. -- Luckily a function invocation makes its own transaction if necessary. -- The mode x'40000'::int corresponds to the PostgreSQL LO mode INV_READ = 0x40000. fd := lo_open($1, x'40000'::int); -- Seek to the end. 2 = SEEK_END. PERFORM lo_lseek(fd, 0, 2); -- Fetch the current file position; since we're at the end, this is the size. sz := lo_tell(fd); -- Remember to close it, since the function may be called as part of a larger transaction. PERFORM lo_close(fd); -- Return the size. RETURN sz; END; $$; </code></pre> <p>Testing it:</p> <pre><code>-- Make a new LO, returns an OID e.g. 1234567 SELECT lo_create(0); -- Populate it with data somehow ... -- Get the length. SELECT get_lo_size(1234567); </code></pre> <p>It seems the LO functionality is designed to be used mostly through the client or through low-level server programming, but at least they've provided some SQL visible functions for it, which makes the above possible. I did a query for <code>SELECT relname FROM pg_proc where relname LIKE 'lo%'</code> to get myself started. Vague memories of C programming and a bit of research for the mode <code>x'40000'::int</code> and <code>SEEK_END = 2</code> value were needed for the rest!</p>
    singulars
    1. This table or related slice is empty.
    1. POGet size of large object in PostgreSQL query?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USEdmund
    1. USEdmund
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POGet size of large object in PostgreSQL query?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload