PostgreSQL server programming extend postgreSQL using postgreSQL server programming to create, test, debug, and optimize a range of user-defined functions in your favorite programming language
This book is for moderate to advanced PostgreSQL database professionals who wish to extend PostgreSQL, utilizing the most updated features of PostgreSQL 9.4. For a better understanding of this book, familiarity with writing SQL, a basic idea of query tuning, and some coding experience in your prefer...
| Otros Autores: | , , , |
|---|---|
| Formato: | Libro electrónico |
| Idioma: | Inglés |
| Publicado: |
Birmingham, England ; Mumbai, [India] :
Packt Publishing
2015.
|
| Edición: | 2nd ed |
| Colección: | Community experience distilled.
|
| Materias: | |
| Ver en Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009628938206719 |
Tabla de Contenidos:
- Cover; Copyright; Credits; About the Authors; About the Reviewers; www.PacktPub.com; Table of Contents; Preface; Chapter 1: What Is a PostgreSQL Server?; Why program in the server?; Using PL/pgSQL for integrity checks; About this book's code examples; Switching to the expanded display; Moving beyond simple functions; Data comparisons using operators; Managing related data with triggers; Auditing changes; Data cleaning; Custom sort orders; Programming best practices; KISS - keep it simple stupid; DRY - don't repeat yourself; YAGNI - you ain't gonna need it; SOA - service-oriented architecture
- Type extensibilityCaching; Wrapping up - why program in the server?; Performance; Ease of maintenance; Improved productivity; Simple ways to tighten security; Summary; Chapter 2: Server Programming Environments; Cost of acquisition; Availability of developers; Licensing; Predictability; Community; Procedural languages; Third-party tools; Platform compatibility; Application design; Databases are considered harmful; Encapsulation; What does PostgreSQL offer?; Data locality; More basics; Transactions; General error reporting and error handling; User-defined functions; Other parameters
- More controlSummary; Chapter 3: Your First PL/pgSQL Function; Why PL/pgSQL?; The structure of a PL/pgSQL function; Accessing function arguments; Conditional expressions; Loops with counters; Statement termination; Looping through query results; PERFORM versus SELECT; Looping Through Arrays; Returning a record; Acting on the function's results; Summary; Chapter 4: Returning Structured Data; Sets and arrays; Returning sets; Returning a set of integers; Using a set returning function; Functions based on views; OUT parameters and records; OUT parameters; Returning records; Using RETURNS TABLE
- Returning with no predefined structureReturning SETOF ANY; Variadic argument lists; A summary of the RETURN SETOF variants; Returning cursors; Iterating over cursors returned from another function; Wrapping up of functions returning cursors; Other ways to work with structured data; Complex data types for the modern world - XML and JSON; XML data type and returning data as XML from functions; Returning data in the JSON format; Summary; Chapter 5: PL/pgSQL Trigger Functions; Creating the trigger function; Creating the trigger; Working on a simple ""Hey, I'm called"" trigger; The audit trigger
- Disallowing DELETEDisallowing TRUNCATE; Modifying the NEW record; The timestamping trigger; The immutable fields trigger; Controlling when a trigger is called; Conditional triggers; Triggers on specific field changes; Visibility; Most importantly - use triggers cautiously!; Variables passed to the PL/pgSQL TRIGGER function; Summary; Chapter 6: PostgreSQL Event Triggers; Use cases for creating event triggers; Creating event triggers; Creating an audit trail; Preventing schema changes; A roadmap of event triggers; Summary; Chapter 7: Debugging PL/pgSQL; Manual debugging with RAISE NOTICE
- Throwing exceptions
