pghelper

pghelper is an R package designed to simplify interactions with PostgreSQL databases. It offers a suite of utility functions for connecting to databases, executing queries, and managing database objects, all while leveraging environment variables for configuration.

Features

• Establish connections to PostgreSQL databases using environment variables.
• Execute SQL queries and retrieve results efficiently.
• List schemas, tables, and columns within the database.
• Manage database objects such as functions, procedures, and triggers.
• Analyze dependencies and references between database objects.

Installation

You can install the development version of pghelper from GitHub:

# Install devtools if you haven't already
install.packages("devtools")

# Install pghelper from GitHub
devtools::install_github("ogrun/pghelper")

Replace "yourusername" with your actual GitHub username.

Usage

Before using the package, ensure that your environment variables for PostgreSQL credentials are set, either in your .Renviron or .env file:

POSTGRES_DB=your_database
POSTGRES_HOST=your_host
POSTGRES_USER=your_username
POSTGRES_PASSWORD=your_password

Load the package and establish a connection:

library(pghelper)

# Connect to the database
conn <- pg_connect()

# List schemas
schemas <- pg_list_schemas()

# Execute a query
result <- pg_query("SELECT * FROM your_table LIMIT 10;")

# Disconnect when done
DBI::dbDisconnect(conn)

📦 Available Functions

Category	Function	Description
Connection	pg_connect()	Connect to PostgreSQL using environment variables
Querying	pg_query()	Execute a SQL query and return a result
	pg_exec()	Execute non-query SQL (e.g. DDL/DML)
Inspection	pg_list_schemas()	List all user-defined schemas
	pg_list_tables()	List tables in a schema
	pg_list_columns()	List columns in a table
	pg_row_count()	Count rows in a table
Metadata	pg_list_functions()	List user-defined functions
	pg_list_procedures()	List stored procedures
	pg_list_triggers()	List table triggers
Dependencies	pg_find_references()	Find views referencing a table/column
	pg_find_function_references()	Find functions/procedures referencing a table/column
	pg_find_trigger_references()	Find trigger functions referencing a table/column
	pg_find_all_references()	Run full impact analysis (views, functions, triggers)

Contributing

Contributions are welcome! Please open issues or submit pull requests for any enhancements or bug fixes.

License

This project is licensed under the MIT License. See the LICENSE file for details.