get_resource_sql: Get PHS Open Data using SQL

In Public-Health-Scotland/phsopendata: Extract Open Data from opendata.nhs.scot

Get PHS Open Data using SQL

Description

Similar to get_resource() but with more options for server-side querying of data. However, this function has a lower maximum row number (32,000 vs 99,999) for returned results.

Usage

get_resource_sql(sql)

Arguments

sql

(character) a single PostgreSQL SELECT query. Must include a resource ID, which must be double-quoted e.g., ⁠SELECT * from "58527343-a930-4058-bf9e-3c6e5cb04010"⁠ Column names must be double-quoted, while character values in filters must be single-quoted. e.g., "Age" = '34' You may need to escape quote marks with ⁠\⁠ to implement this. e.g., ⁠sql = "SELECT * FROM \"<res_id>\" WHERE \"Age\" = '34'"⁠.

Value

a tibble with the query results. Only 32,000 rows can be returned from a single SQL query.

See Also

get_resource() for downloading a resource without using a SQL query.

Examples

sql <- "
   SELECT
     \"TotalCancelled\",\"TotalOperations\",\"Hospital\",\"Month\"
   FROM
     \"bcc860a4-49f4-4232-a76b-f559cf6eb885\"
   WHERE
     \"Hospital\" = 'D102H'
"
df <- get_resource_sql(sql)

# This is equivalent to:
cols <- c(
  "TotalCancelled", "TotalOperations",
  "Hospital", "Month"
)
row_filter <- c(Hospital = "D102H")

df2 <- get_resource(
  "bcc860a4-49f4-4232-a76b-f559cf6eb885",
  col_select = cols,
  row_filters = row_filter
)

Public-Health-Scotland/phsopendata documentation built on April 14, 2025, 11:45 a.m.