I have been looking around for a while, for example in The RMarkdwon Definitive Guide or elsewhere, but found no satisfactory and clear description nor very clear example to connect to a PostgreSQL database. The way the information in that definitive guide reads to me seems kind of meaningless to me for some reason, such that I do not understand it.

The main piece of information I found is this (with {r setup} above it according to the information):

library(DBI)
db = dbConnect(RSQLite::SQLite(), dbname = "sql.sqlite")
knitr::opts_chunk$set(connection = "db")

The library(DBI) part of course I get, but not the rest, except that knitr is a pack used for certain purposes, and some things here and there). Basically, I do not know how to set this up for PostgreSQL.

So what would be a good example for a first PostgreSQL setup chunk?

(As a sidenote, because I thought I wasted too much time, I just used RPostgres whenever I wanted. But because I thought that using SQL chunks would have greater advantages, I checked again. Maybe, in the end, I would be better off without the direct SQL chunks, but if I get to understand it sufficiently, maybe that would pay off, for example in having to type less or in having a nicer looking document or so.)

CodePudding user response：

The dbConnect line is about connecting to your database; in the example, it's an in-memory SQLite database, but you'll need to modify it to connect to your PostgreSQL instance. There's an example at Read/write Postgres large objects with DBI & RPostgres,

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

(change the details to match your database)

The knitr::opts_chunk part is setting an option to knitr, so that you don't need to specify connection = "db" in every code chunk.