Is it possible to run Postgres backslash commands via RPostgresql?

Question

Postgres has many convenient "backslash" commands (e.g \dt, \du, \l, etc). I would like to run these via the RPostgresql interface. I have tried the following:

drv = dbDriver("PostgreSQL")
con <- dbConnect(drv, 
                 dbname = "my_database", 
                 host = "**********", 
                 port = ****, 
                 user = "******", 
                 password = "******")

dbExecute(con, "\\dt")

# Close PostgreSQL connection  
dbDisconnect(con) 

However, I get the following error:

Error in postgresqlExecStatement(conn, statement, ...) : 
RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at or 
near "\"
LINE 1: \dt
        ^
)

Is it possible to run these from RPostgresql? Or are backslash commands restricted to command line psql only?

Answer 1

The backslash-commands are interpreted by the psql CLI tool, the underlying client libraries don't know what they are are nor does the PostgreSQL server. RPostgresql will be using the client libraries (or perhaps directly speaking the PostgreSQL protocol to communicate with the server) so the backslash-commands won't be available.

However, the backslash-commands are mostly just convenient wrappers for queries that access the PostgreSQL system tables. psql has a -E switch that will let you see these queries:

-E
--echo-hidden
Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on.

So you can:

• start psql with psql -E ...
• enable from inside psql \set ECHO_HIDDEN on

And then, you'll see what postgresql query is executed when using commands such as \dt:

=> \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

                       List of relations
...

You can then use this SQL query like any other query in RPostgreSQL.