Re: computing and updating the size of a table with large objects

["Daniel Verite" <daniel(at)manitou-mail(dot)org>]

	Marco Bizzarri wrote:

>            Tabella "public.documenti"
>  Colonna |         Tipo          | Modificatori
> ---------+-----------------------+--------------
>  id_doc  | character varying(50) | not null
>  file    | oid                   |
>  kind    | integer               | not null
>  size    | bigint                |
> Indici:
>     "documenti_pkey" chiave primaria, btree (id_doc, kind)
> 
> (sorry for the mixed language output)
> 
> I need to populate the size attribute with the size of the large object in file.

You can get the sizes from pg_largeobject, this way:

SELECT id_doc, sum(length(data)) as filesize
FROM documenti, pg_largeobject
WHERE documenti.file = pg_largeobject.loid
GROUP BY id_doc;

-- 
 Daniel
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org