I.e. the last (non-option, but optional) argument is the dbname (plus optional username).
IIUC, the psql engine puts the -c command option last, making it effectively impossible to specify the dbname in the as the last argument via engine.opts:
This PR adds the psql engine to the engines that use a code, opts order. Since -c command is an option argument as far as psql is concerned, reversing the order shouldn't have any negative repercussions.
Full disclosure: The practical significance of this change is not very high as it turns out, since dbname and username can be specified via options as well (via -d dbname and -U username, respectively). With -d service=myservice it is also possible to use a connection service file, which is what prompted me to look into this.
Nevertheless, the psql [option...] [dbname [username]] signature is quite common (AFAICT) and this change enables that usage.
example
[connection service file](https://www.postgresql.org/docs/current/libpq-pgservice.html)
```
[knitr]
host=localhost
user=postgres
dbname=knitr
```
[.pgpass password file](https://www.postgresql.org/docs/current/libpq-pgpass.html)
```
localhost:5432:*:postgres:mysecretpassword
```
Rmd file
````rmd
---
title: "psql with knitr"
output: github_document
---
```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE, error = FALSE, warning = FALSE)
```
```{bash}
docker run --name pg-knitr -d --rm -p 5432:5432 -e POSTGRES_DB=knitr -e POSTGRES_PASSWORD=mysecretpassword postgres
docker ps
sleep 5 # wait for container to start
```
`psql` chunk works with `service`.
```{psql, engine.opts='service=knitr'}
SELECT 1 AS col;
```
```{bash}
docker stop pg-knitr
```
````
Result:
````markdown
psql with knitr
================
``` bash
docker run --name pg-knitr -d --rm -p 5432:5432 -e POSTGRES_DB=knitr -e POSTGRES_PASSWORD=mysecretpassword postgres
docker ps
sleep 5 # wait for container to start
```
## 01405d08e09f5537f07c49bb2fb6a338ac60b0cd2f58b5eda6a2d4f2648341af
## CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
## 01405d08e09f postgres "docker-entrypoint.s…" 1 second ago Up Less than a second 0.0.0.0:5432->5432/tcp pg-knitr
`psql` chunk works with `service`.
``` sql
SELECT 1 AS col;
```
## col
## -----
## 1
## (1 row)
``` bash
docker stop pg-knitr
```
## pg-knitr
````
dpprdan
commented
1 year ago
The psql function signature is
I.e. the last (non-option, but optional) argument is the
dbname(plus optionalusername).IIUC, the psql engine puts the
-c commandoption last, making it effectively impossible to specify thedbnamein the as the last argument viaengine.opts:https://github.com/yihui/knitr/blob/a0dfe2b9542a48018e4ab83eab3ebc50ebb647a8/R/engine.R#L142-L145
This PR adds the psql engine to the engines that use a
code, optsorder. Since-c commandis an option argument as far as psql is concerned, reversing the order shouldn't have any negative repercussions.Full disclosure: The practical significance of this change is not very high as it turns out, since dbname and username can be specified via options as well (via
-d dbnameand-U username, respectively). With-d service=myserviceit is also possible to use a connection service file, which is what prompted me to look into this.Nevertheless, the
psql [option...] [dbname [username]]signature is quite common (AFAICT) and this change enables that usage.example
[connection service file](https://www.postgresql.org/docs/current/libpq-pgservice.html) ``` [knitr] host=localhost user=postgres dbname=knitr ``` [.pgpass password file](https://www.postgresql.org/docs/current/libpq-pgpass.html) ``` localhost:5432:*:postgres:mysecretpassword ``` Rmd file ````rmd --- title: "psql with knitr" output: github_document --- ```{r setup, include=FALSE} knitr::opts_chunk$set(echo = TRUE, error = FALSE, warning = FALSE) ``` ```{bash} docker run --name pg-knitr -d --rm -p 5432:5432 -e POSTGRES_DB=knitr -e POSTGRES_PASSWORD=mysecretpassword postgres docker ps sleep 5 # wait for container to start ``` `psql` chunk works with `service`. ```{psql, engine.opts='service=knitr'} SELECT 1 AS col; ``` ```{bash} docker stop pg-knitr ``` ```` Result: ````markdown psql with knitr ================ ``` bash docker run --name pg-knitr -d --rm -p 5432:5432 -e POSTGRES_DB=knitr -e POSTGRES_PASSWORD=mysecretpassword postgres docker ps sleep 5 # wait for container to start ``` ## 01405d08e09f5537f07c49bb2fb6a338ac60b0cd2f58b5eda6a2d4f2648341af ## CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES ## 01405d08e09f postgres "docker-entrypoint.s…" 1 second ago Up Less than a second 0.0.0.0:5432->5432/tcp pg-knitr `psql` chunk works with `service`. ``` sql SELECT 1 AS col; ``` ## col ## ----- ## 1 ## (1 row) ``` bash docker stop pg-knitr ``` ## pg-knitr ````