985-dbplyr-sql-translations.Rmd

# Appendix _ Dplyr to SQL translations {#chapter_appendix-dplyr-to-postres-translation}

> You may be interested in exactly how the DBI package translates R functions into their SQL quivalents -- and in which functions are translated and which are not.  
This Appendix answers those questions.  It is based on the work of Dewey Dunnington ([\@paleolimbot](http://twitter.com/paleolimbot)) which he published here: 
> 
> https://apps.fishandwhistle.net/archives/1503 
>
>  https://rud.is/b/2019/04/10/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends/

## Overview

These packages are called below:
```{r setup, echo=TRUE, message=FALSE, warning=FALSE}
library(tidyverse)
library(dbplyr)
library(gt)
library(here)
library(sqlpetr)
```
list the DBI functions that are available:
```{r}
names(sql_translate_env(simulate_dbi()))
sql_translate_env(simulate_dbi())
```

```{r}
source(here("book-src", "dbplyr-sql-function-translation.R"))
```

Each of the following dbplyr back ends may have a slightly different translation:

```{r}
translations %>%
  filter(!is.na(sql)) %>% count(variant)
```
Only one postgres translation produces an output:
```{r}
psql <- translations %>%
  filter(!is.na(sql), variant == "postgres") %>%
  select(r, n_args, sql) %>%
  arrange(r)

# sp_print_df(head(psql, n = 40))
sp_print_df(psql)
```