SQL 在 PostgreSQL 查询中获取大对象的大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10169309/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get size of large object in PostgreSQL query?
提问by Bob
I would like to obtain the byte size of a blob.
我想获取 blob 的字节大小。
I am using Postgresql and would like to obtain the size using an SQL query. Something like this:
我正在使用 Postgresql 并希望使用 SQL 查询获取大小。像这样的东西：
SELECT sizeof(field) FROM table;
Is this possible in Postgresql?
这在 Postgresql 中可能吗？
Update: I have read the postgresql manual and could not find an appropriate function to calculate the file size. Also, the blob is stored as a large object.
更新：我已阅读 postgresql 手册，但找不到合适的函数来计算文件大小。此外，blob 存储为一个大对象。
回答by Edmund
Not that I've used large objects, but looking at the docs: http://www.postgresql.org/docs/current/interactive/lo-interfaces.html#LO-TELL
不是我使用过大对象，而是查看文档：http: //www.postgresql.org/docs/current/interactive/lo-interfaces.html#LO-TELL
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:
我认为您必须使用与某些文件系统 API 要求相同的技术：寻找到最后，然后告诉位置。PostgreSQL 具有似乎包装内部 C 函数的 SQL 函数。我找不到太多文档，但这有效：
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(, 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;
$$;
Testing it:
测试它：
-- 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);
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 SELECT relname FROM pg_proc where relname LIKE 'lo%'to get myself started. Vague memories of C programming and a bit of research for the mode x'40000'::intand SEEK_END = 2value were needed for the rest!
看起来 LO 功能被设计为主要通过客户端或通过低级服务器编程使用，但至少他们已经为其提供了一些 SQL 可见函数，这使得上述成为可能。我做了一个查询SELECT relname FROM pg_proc where relname LIKE 'lo%'让自己开始。其余的需要对C 编程的模糊记忆以及对模式x'40000'::int和SEEK_END = 2值的一些研究！
回答by vstan
You could change your application to store the size when you create the large object. Otherwise you can use a query such as:
创建大对象时，您可以更改应用程序以存储大小。否则，您可以使用查询，例如：
select sum(length(lo.data)) from pg_largeobject lo
where lo.loid=XXXXXX
You can use also the large object API functions, as suggested in a previous post, they work ok, but are an order of magnitude slower than the select method suggested above.
您也可以使用大对象 API 函数，如前一篇文章中所建议的，它们工作正常，但比上面建议的 select 方法慢一个数量级。
回答by Chris Travers
select pg_column_size(lo_get(lo_oid)) from table;
Gives you the size in bytes.
为您提供以字节为单位的大小。
If you want pretty printing:
如果你想要漂亮的打印：
select pg_size_pretty(pg_column_size(lo_get(lo_oid))::numeric) from table;
回答by i-g
Try length()or octet_length()
尝试length()或octet_length()
回答by rafahead
This is my solution:
这是我的解决方案：
select
lo.loid,
pg_size_pretty(sum(octet_length(lo.data)))
from pg_largeobject lo
where lo.loid in (select pg_largeobject.loid from pg_largeobject)
group by lo.loid;
