我想获得blob的字节大小.
我正在使用Postgresql,并希望使用SQL查询获取大小.像这样的东西:
SELECT sizeof(field) FROM table;
这在Postgresql中可行吗?
不是说我使用过大型对象,而是查看文档:
http://www.postgresql.org/docs/current/interactive/lo-interfaces.html#LO-TELL
我认为你必须使用与某些文件系统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($1,x'40000'::int); -- Seek to the end. 2 = SEEK_END. PERFORM lo_lseek(fd,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可见功能,这使得上述功能成为可能.我对SELECT relname FROM pg_proc进行了查询,其中relname LIKE’lo%’让我自己开始.对于C编程的模糊记忆以及对模式x’40000′:: int和SEEK_END = 2值的一些研究是其余的需要!