The second beta version of pgEdit 1.1 is now available. This version adds some enhancements to PHP syntax coloring and includes a few minor bug fixes. See the release notes included in the distribution for complete details. A summary of new pgEdit 1.1 features is available here.

pgEdit 1.1 beta is free of all demonstration limitations through the end of May.

See the download page for software and system requirements.

The first beta of pgEdit 1.1 is now available. New features and enhancements include:

• Error information is collected with each execution. New commands to jump to each error location in the file even if lines are added or removed.
• Improved integration with psql; most psql commands are now supported including \connect and \copy.
• PHP integration. PHP can act as a preprocessor for SQL code or perform other scripting tasks. Mixed syntax coloring of PHP and SQL in the same file is supported including syntax coloring for pl/PHP stored procedures. PHP 5 is included with the distribution, so no extra configuration is required to use this feature. See PHP example.
• Faster and better syntax coloring. stdin data for COPY is ignored for coloring (unless followed a PHP tag).
• New options for execution and output. Output can be sent to a file and then opened automatically with pgEdit, your web browser, or other applications.
• Improved user interface with the editor and output panes available in the same view.

pgEdit 1.1 beta is free of all demonstration limitations through the end of May.

A preview of coming attractions in pgEdit 1.1: PHP support. PHP can be used in two different ways. First, it can be used to write plPHP stored procedures. In this case pgEdit looks for a $php$ dollar quote or the LANGUAGE parameter with 'plPHP' at the start of the function definition. If this is found, PHP syntax coloring is used for the body of the function.

PHP can also be used as a preprocessor to parameterize and dynamically generate SQL. pgEdit recognizes opening and closing PHP tags and automatically switches syntax coloring between PHP and SQL. The PHP output from the mixed syntax file is then passed to psql for processing.

Two new mailing lists have been added for pgedit.com. The news mailing list is a low volume list for news and product announcements. The pgEdit list is a general support and discussion list for pgEdit.

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.

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.

After several months of testing, feedback, and improvements, pgEdit 1.0 is now available. This version includes a free trial period through February 14, 2005. During this time all pgEdit features are available with no demonstration mode limitations.