PostgreSQL Server Programming: Take your skills with PostgreSQL to a whole new level with this fascinating guide to server programming. A step by step approach with illuminating examples will educate you in the full range of possibilities.

One of biggest factors in deciding what technology is used in the application stack is the cost of acquisition. I've seen many application architectures drawn on a whiteboard where the technical team was embarrassed to show me, but they justified the design by trying to keep software licensing costs down. When it comes to the database environment, the usual suspects are Oracle, SQL Server, MySQL, and PostgreSQL. Oracle, the dominant player in the database space, is also the most costly. At the low end, Oracle does have reasonably priced offering and even a free express edition, but they are limited. Most people have needs beyond the low priced offerings and fall into the enterprise sales machine of Oracle. This usually results in a high price quote that makes your CFO fall out of his chair and you're back to designing your solution to keep your licensing costs down.

Then comes Microsoft SQL Server. This is your first reasonably viable option. The pricing is listed on the...

This has been one of the most hilariously fun parts of my development life. I recently recommended using PostgreSQL for a reporting system in a local company. The company in question wanted to know if they chose PostgreSQL, would anyone on staff be able to maintain it. So I began to interview the developers to find out about their experience with PostgreSQL.

Me: Do you have any experience with PostgreSQL?

Developer 1: Yes, I used it at the last job for a product fulfillment project, but I don't think very many people have that experience. We should probably stick with MySQL.

Me: Do you have any experience with PostgreSQL?

Developer 2: Yes, I used it at the last job for a reporting project, but I don't think very many people have that experience. We should probably stick with MySQL.

After interviewing all seven developers that were influential on the project, I found that the only person without hands-on experience with PostgreSQL was the project manager. Since the project...

About two months after Oracle bought MySQL, they announced a plan that divided the development into two camps. There would now be a MySQL community edition and a professional version. The community edition would no longer gain any new features, and the professional version would become a commercial product.

There was a vast and thunderous sucking sound in the open source community, as they thrashed wildly about to find a new platform for Free and Open Source (FOSS) development.

Oracle immediately (in about 2 weeks) countermanded the order, and declared that things would stay as they were for the indefinite future. Those with short memories, forgiving hearts, or who just weren't paying attention went on about their business. Many other open source projects either switched to PostgreSQL or suddenly grew PostgreSQL database support.

Today we have MySQL and MySQL Enterprise Edition. If you want "backup, high availability, enterprise scalability, and the MySQL Enterprise monitor", you...

This section could just as well have been titled "standards compliance," but I decided against it because the benefits of standards compliance in corporate projects are not obvious. The limitations of the common databases are well documented, and I will show you a few websites in a moment where you can make a comparison of who has the most "unintended behavior". I encourage you to read the material while thinking to yourself about the question, "Which method of feature development is most likely to make my application break in the future?"

http://www.sql-info.de/postgresql/postgres-gotchas.html

http://www.sql-info.de/mysql/gotchas.html

Spoiler alert: Stricter adherence to standards comes at the cost of not allowing ambiguous behavior. Not allowing ambiguous behavior makes the developer's life more difficult. Making the developer's life more difficult ensures that the interpretation of the commands that the developer gives will not change later, breaking the application.

Just how...

Oracle and SQL Server don't have a community. Please understand when I say that, I mean that the chance that you will get to talk to a developer of the core database is about the same as your chance of winning the lottery. By the time you do, it's probably because you found a bug so heinous that it couldn't be ignored, and the only person who can understand your report is the guy that wrote the code in question. They have paid technical support, and that support has proven in my experience to be generally competent, but not stellar. I have had to work around the problem that I originally requested help with about 40 percent of the time.

Contrast that to MySQL and PostgreSQL, where just about anybody can speak to just about anybody else all day long. Many of the core developers of both the platforms can be found on IRC, met at conventions, contacted for contract development work, and for the most part, bribed remarkably easily with beer (hint, hint, wink, wink, nudge, nudge).

They...

SQL Server allows you to create DLLs in any language that produces CLR. These DLLs must be loaded into the server at boot time. To create a procedure at run time and have it be immediately available, the only choice is the built in SQL dialect, Transact SQL (TSQL).

MySQL has a feature called plugins. One of the legal plugin types is a procedural language. Several languages have been tooled to work with MySQL via the plugin system, including most of the popular ones such as PHP and Python. These functions cannot be used for stored procedures or triggers, but they can be invoked from the common SQL statements. For the rest, you are stuck with the built-in SQL.

PostgreSQL has full support for additional procedural languages, which can be used to create any legal entity in the database that can be created with PL/pgSQL. The language can be added (or removed) from a running version of PostgreSQL and any function defined using that language may also be created or dropped while...

Now you know a few things about the PostgreSQL environment, as well as some things that will help you in the unforeseeable future. PostgreSQL is built to handle your needs, but more importantly, it is built not to change underneath you in the future.

We touched a little on the environment and called out some of the more important things to keep in mind while programming on the server in PostgreSQL. Don't worry too much if you don't remember all of it. It is fine to go on to the next chapter, where we actually start making some useful functions. Then come back and review this chapter when you have a clearer understanding of the features available to the function writer.