pg_sql: Run an SQL command on a selected database

In mbedward/CERMBlidarpostgis: Store and retrieve processed LIDAR data from a PostGIS database

Description

This function allows you to run arbitrary SQL commands on a selected database via the active PostgreSQL server. It is an alternative to connecting to the database via the RPostgreSQL package or similar. If the SQL commands are provided as a character vector, they are run by first writing them to a temporary file which is passed to the command line program psql.exe. Multiple commands can be provided in concatenated form as either a one-element character vector, or as a multi-element vector. In both cases, each command must be followed by a semicolon as per normal SQL syntax (but see Details for an exception to this rule in the case of special psql commands).

Usage

pg_sql(dbsettings, command = NULL, file = NULL, quiet = TRUE)

Arguments

dbsettings	A named list of database connection settings returned by db_connect_postgis or db_create_postgis.
command	A valid SQL command as a character string or glue object. Can also be a special psql command (see Details). Not required if the file argument is used to read commands from a file.
file	The path and name of a file containing the SQL command(s) to run. Ignored if command is provided.
quiet	If TRUE the output from the psql helper application is returned invisibly; if FALSE (default) the output is returned explicitly.

Details

The psql command line program installed with PostgreSQL supports a set of special commands in addition to standard SQL statements. For example, '\d mytable' will return a description of the specified table while '\dt' will list all tables in the database. These commands can be issued via the pg_sql function and do not require a following semi-colon. Note however that R will require the leading backslash to be 'escaped', e.g. '\dt' otherwise obscure error messages will result.

Value

Text of the database status message after running the command.

See Also

db_connect_postgis db_create_postgis

Examples

## Not run: 
Sys.setenv(PGPASSWORD = "cermb")

dbsettings <- db_connect_postgis("cermb_lidar")

pg_sql(dbsettings, "SELECT COUNT(*) AS NRECS FROM FOO;")

## End(Not run)

mbedward/CERMBlidarpostgis documentation built on Nov. 14, 2021, 7:15 p.m.