How to write R object to Postgres large object

[ColinFay]

Cross-posted with https://stackoverflow.com/questions/70271590/read-write-postgres-large-objects-with-dbi-rpostgres

I'm trying to use the large object feature from Postgres https://www.postgresql.org/docs/10/largeobjects.html but I have a hard time wrapping my head around converting R object and writing them to the db.

Here is what I have tried so far:

# Getting the db
docker run --rm --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d -p 5433:5432 postgres

library(DBI)
con <- dbConnect(
  RPostgres::Postgres(),
  dbname = "postgres",
  host = "localhost",
  port = 5433,
  user = "postgres",
  password = "mysecretpassword"
)

Creation works :

> dbGetQuery(con, "SELECT lo_create(1234);")
  lo_create
1      1234

But then I have a hard time figuring out how to write an R object to this large object.
For example, how would I write mtcars as a large object in Postgres using {DBI} and {RPostgres}?

And then, how do I read it back again in R?

[krlmlr]

Thanks. Can you use lo_from_bytea(), lo_put() and lo_get(), perhaps with dbQuoteLiteral() to quote a blob value?

[jjesusfilho]

I use lo_import() to insert images as blobs in PostgreSQL:

DBI::dbExecute(conn, "create table my_table( id int, image oid)")

DBI::dbGetQuery(conn, "insert into my_table (id, image)
                                   values(1, lo_import('absolute_path_to_image_file')) returning image")
      image
1      1234

In order to bring the image to R, I use lo_get():

df <-  DBI::dbGetQuery(conn, "select lo_get(1234) as image")

But those functions only work on the server side, i.e., the file must be on the same server as PostgreSQL. Also, the user must be a superuser. It would be great if someone built an R package to work on the client side: client interfaces.

[krlmlr]

There is a libpq function lo_import() that accepts files on the client system. Does the R interface need to do more than calling this function?

[toppyy]

Added a PR that introduces the postgresImportLargeObject() for importing large objects from client side. I used libpq's lo_import_with_oid() so you can import to a specific oid if need be.