This article describes a simple class that can be used to call PostgreSQL functions. Using the class has a number of advantages:

1. PostgreSQL functions can be called like they are PHP method functions with no special syntax.
2. The statements are automatically prepared giving faster execution for multiple calls.
3. No string escaping is needed for prepared statements.
4. The prepared statements are automatically deallocated when the object is destructed.

Drupal is an excellent PHP content management system. It supports both MySQL and PostgreSQL as the back end database for content management. Unfortunately, PostgreSQL comes up short in the documentation department: it is not even mentioned in the Drupal INSTALL.txt file. So here we do our small part for the cause and provide some instructions to get Drupal up and running with PostgreSQL.

If you have PostgreSQL databases hosted remotely, SSH tunneling provides a convenient and secure communications mechanism. This approach avoids issues with SSL certificate configuration and obviates the need to modify the pg_hba.conf file to setup host permissions. The primary disadvantage is that some hosting providers do not allow SSH access (or only provide it on more expensive accounts).

Here is an example to setup SSH tunneling for PostgreSQL:

ssh -L 5555:localhost:5432 -l remoteuser myhost.com

This forwards connections made on localhost port 5555 to myhost.com port 5432. The -l parameter is your user name for shell access on the remote system (not your PostgreSQL user name).

So to use pgEdit, pgAdmin, or any other PostgreSQL utility, use localhost and port 5555 along with the same login credentials you would normally provide to connect to the database.

If you need to produce documentation with SQL source code, pgEdit has several commands you might find useful. The Copy for HTML command copies the selected source text to the clipboard in HTML format. The HTML consists of a PRE element with SPAN elements to describe the syntax coloring. The CSS From Syntax Colors command can be used to copy a cascading style sheet (CSS) fragment to the clipboard with your current color settings. This allows you to modify the syntax coloring used in any HTML page by having them all refer to the same CSS file.

The Copy for DocBook command performs a similar function in creating a DocBook ProgramListing element with syntax coloring information on the clipboard. The XSL From Syntax Colors command puts the XSL style sheet information for the current color settings on the clipboard. This information, along with the CSS style information, allows DocBook to be used to produce syntax colored program listings for both HTML and PDF output.

Here is a short trigger function to illustrate the HTML output:

PostgreSQL provides a number of access control options, but granting privileges en masse can be difficult. For example, the GRANT command requires that each table be explicitly listed in the statement. To overcome this, Andrew Hammond and Tim Goodaire created some SQL functions to simplify this and several other administration tasks related to access control.

There has been significant discussion on the PostgreSQL hackers list about improving the GRANT and REVOKE syntax, so perhaps there will be other options available in the near future.

pgEdit has three commands to execute variations of PostgreSQL's EXPLAIN command. In PostgreSQL this command is used to provide execution plan information about the SQL statement. All three forms of the pgEdit command make it easy to use EXPLAIN for anything in your file without making modifications. The pgEdit Explain command simply executes the current SQL line containing the cursor with PostgreSQL's EXPLAIN keyword prepended to the statement. Similarly, the Explain Analyze command prepends EXPLAIN ANALYZE which actually executes the command giving actual rather than estimated statistics.

Editors are for application development

There are many PostgreSQL development and administration tools available, and some are free. But pgEdit is the first editor optimized solely for PostgreSQL development. The typical PostgreSQL tool gives a big tree view of databases, schemas, tables, functions and so on (pgEdit will eventually have one too). This is fine for administration or simple development tasks, but gets in the way of developing complex database applications. The key issue is that an editor provides locality - you can group related tables, functions, triggers, and other database objects together in a file. As with other software development, you can build version controlled files of logically related objects rather than trying to work from a from a big tree view of objects listed by type and in alphabetical order.