在PostgreSQL查询中获取大对象的大小？

我认为你必须使用与某些文件系统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值的一些研究是其余的需要！