how can I access large object store on foreign table

Question

I have set up postgres_fdw to access a 'remote' database (in fact its on the same server). Works fine. Except one of the columns is the oid of a large object, how can I read that data?

You could use a view that converts the large object to a `bytea`, but that is probably not what you want. I don't think that there is another solution. — Laurenz Albe, Jun 12 '18 at 05:36

score 1 · Answer 1 · answered Jun 12 '18 at 15:24

I worked out how to do this. The large object store can also be accessed via the pg_largeobject table. So I did

create foreign table if not exists global_lo (
    loid oid not null,
    pageno integer not null,
    data bytea 
)
server glob_serv options(table_name 'pg_largeobject', schema_name 'pg_catalog');

Now I can read a large object (all of it, I cannot stream etc) with

select data from global_lo where loid = 1234

Haroldo_OK · Answer 2 · 2019-07-15T18:29:21.723

If you have access to the foreign database, you could create a view on it to convert the lobs to either bytea or text so they can be used by the local database.

On the foreign database, you would create the view:

drop view if exists tmp_view_produto_descricao;

create view tmp_view_produto_descricao as
select * from (
    select dado.*, lo_get(dado.descricaoExtendida_oid) as descricaoEstendida
    from (
        select 
            itm.id as item_id, 
            case when itm.descricaoExtendida is Null then null else Cast(itm.descricaoExtendida as oid) end descricaoExtendida_oid
        from Item itm
        where itm.descricaoExtendida is Not Null
        and   Cast(itm.descricaoExtendida as Text) != ''
    ) dado
) dado
where Cast(descricaoEstendida as Text) != '';

On the local database, you would declare the foreign view so you could use it:

create foreign table tmp_origem.tmp_view_produto_descricao (
    item_id bigint,
    descricaoExtendida_oid oid,
    descricaoEstendida bytea
) server tmp_origem options (schema_name 'public');

This is slightly messier and wordier, but will give you better performance than you would get by acessing pg_largeobject directly.

how can I access large object store on foreign table

2 Answers2