Functions represent an efficient and secure method to manage CRUD operations in database applications. A CRUD function encapsulates data complexity and provides a standard technique to work with data access methods. Developers invest significant time and effort to write CRUD functions in a standardize style.
This proposal aims to present a tool to generate essential CRUD functions to aid PostgreSQL's developers to standardize coding style and simplify the time to analyze table structures.
The tool uses PLPGSQL to inspect a table's metadata (attributes, data types, pk fields, fk fields and constraints) and combines them with CRUD templates to produce the following CRUD functions:
Create. This function identifies the serial types fields (small serial, serial, big serial) and excludes them from the list of fields-values to insert.
Read 1. This function supports SELECT from multiple tables through foreign key (FK) fields to identify foreign tables and fields relationships. The SELECT clause includes the attributes from the foreign tables. The function contains the options LIMIT and OFFSET
Read 2. This function adds the WHERE clause to restrict the record selection by key values to the Read 1 function.
Update. The key fields are used in the WHERE clause to restrict the update operation. The key attributes are excluded from the SET list
Delete. The key fields are used in the WHERE clause to restrict the delete operation
The tool also generates a CREATE VIEW statement and supports SELECT from multiple tables through foreign key (FK) fields to identify foreign tables and field relationships. The SELECT clause includes the columns from the foreign tables.
12 сен 2024
