获取PostgreSQL查询中大对象的大小? [英] Get size of large object in PostgreSQL query?
问题描述
我想获取blob的字节大小。
I would like to obtain the byte size of a blob.
我正在使用Postgresql,并希望通过SQL查询获取大小。像这样:
I am using Postgresql and would like to obtain the size using an SQL query. Something like this:
SELECT sizeof(field) FROM table;
在Postgresql中可能吗?
Is this possible in Postgresql?
更新:我已经阅读了postgresql手册,找不到合适的函数来计算文件大小。另外,blob存储为大对象。
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.
推荐答案
不是我使用过大对象,而是查看文档: http://www.postgresql.org/docs/current/interactive/lo -interfaces.html#LO-TELL
Not that I've used large objects, but looking at the docs: http://www.postgresql.org/docs/current/interactive/lo-interfaces.html#LO-TELL
我认为您必须使用某些文件系统API要求的相同技术:搜寻到最后,然后告诉位置。 PostgreSQL的SQL函数似乎包装了内部C函数。我找不到太多文档,但这行得通:
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:
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;
$$;
测试它:
-- 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);
LO功能似乎主要是通过客户端或通过低级服务器编程使用的,但至少他们为其提供了一些SQL可见函数,这使得上述操作成为可能。我对pg_proc的 SELECT relname进行了查询,其中的relname喜欢'lo%'
以使自己入门。 C语言编程的模糊记忆以及对 x'40000':: int
和 SEEK_END = 2
模式的一些研究剩下的就需要值!
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'::int
and SEEK_END = 2
value were needed for the rest!
这篇关于获取PostgreSQL查询中大对象的大小?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!