Functions, Stored Procedures, and Triggers
Apress eBooks · 2006
In this chapter, we looked at ways in which we can extend the functionality of PostgreSQL queries. We have seen that PostgreSQL provides many operators and functions that we can use to refine queries and extract information. The procedural languages supported by PostgreSQL allow us to develop quite sophisticated server-side processing by writing procedures in PL/pgSQL, SQL, and other languages. This provides the opportunity for the database server to implement complex application functionality independently of the client. Stored procedures are stored in the database itself and may be called by the application or, in the form of triggers, called automatically when changes are made to database tables. This gives us another means of enforcing referential integrity. For simple referential integrity, it’s generally best to stick to constraints, as they are more straightforward, efficient, and less error-prone. The power of triggers and stored procedures comes when your declarative constraints become very complex, or you wish to implement a constraint that is too complex for the declarative form. Now that we have covered some advanced PostgreSQL techniques, in the next chapter, we will move on to the topic of how to care for a PostgreSQL database.