Is there any way to call PostgreSQL tablefunc module in R?

Question

I have some PostgreSQL/PostGIS tablefunc queries that I want to call in R environnement. Is there any way to perform this?

Ex :

dbGetQuery(con, "SELECT * FROM CROSSTAB(
'SELECT factor1, factor2, ROUND(SUM(ST_AREA(geom))::numeric, 3) FROM table GROUP BY factor1, factor2 ORDER BY 1,2',
'SELECT factor2 FROM table GROUP BY factor2 ORDER BY factor2 '
) AS ct("factor1" varchar, "factor2value1" varchar, "factor2value2" varchar, "factor2value3" varchar)
")

score 0 · Accepted Answer · answered Apr 26 '19 at 23:04

Any compliant Postgres query can work if you connect successfully to Postgres. Hence, if an SQL statement works in Postgres (i.e., psql, pgAdmin) it should work in a client like R that connects to it. Note: the crosstab requires an extension. Simply escape the double quotes in SQL statement with backslashes to compile as a character in R:

sql <- "SELECT * 
        FROM CROSSTAB(
           'SELECT factor1, factor2, 
                   ROUND(SUM(ST_AREA(geom))::numeric, 3) 
            FROM table 
            GROUP BY factor1, factor2 
            ORDER BY 1,2'
           ,
           'SELECT factor2 
            FROM table 
            GROUP BY factor2 
            ORDER BY factor2 '
         ) AS ct(\"factor1\" varchar, \"factor2value1\" varchar,
                 \"factor2value2\" varchar, \"factor2value3\" varchar)"

dbGetQuery(con, sql)

Is there any way to call PostgreSQL tablefunc module in R?

1 Answers1