pgEdit is a TextMate bundle for working with PostgreSQL relational databases. It supports PostgreSQL specific syntax coloring (e.g. dollar quoting), SQL execution, context specific help, completion, and recognition of SQL language constructs (tables, functions, triggers, etc.). pgEdit uses psql for executing SQL and takes advantage of many psql features.
pgEdit can execute SQL in three ways: (1) the entire file, (2) the selected text, and (3) the current SQL line containing the cursor. A SQL line can extend over any number of editor lines; pgEdit uses TextMate scopes to determine the start and end of the SQL command accounting for strings, comments, dollar quotes, etc. This makes it very convenient to modify complex SQL source objects and evaluate them in psql with a single keystroke.
pgEdit copyright © 2010-2011 by John DeSoi, Ph.D. For more information and the latest version visit pgedit.com.
In order to use pgEdit, psql must be in your application search path. If you do not install PostgreSQL in a standard location, you can modify the PATH shell variable in TextMate preferences (Advanced tab) to include the PostgreSQL 'bin' directory.
You can also specify the full path to psql by setting the PGEDIT_PSQL_PATH shell variable in TextMate preferences. If psql is in your path but has a non-standard name (e.g. "psql90"), you can put just the executable name in PGEDIT_PSQL_PATH.
pgEdit will prompt you whenever a database connection is needed. Database connections are specified in URL syntax with the following format:
//user@host:port/database/optional/search/pathMost elements are optional. To connect to a local database on the default port under your current user account, all that needs to be entered is the database name. If you specify elements after the database name, they are used to set the schema search path after a successful connection. For example
//bob@/accounting/ledger/publicwould connect as user 'bob' to database 'accounting' on the local host and set the schema search path to 'ledger, public' after connecting.
pgEdit will prompt you for a password if PostgreSQL requests one. Passwords are saved in the Mac OS keychain. The connection URL is saved as an extended attribute for the document so it will be available as the default if the document is used in a future session. You will be prompted for the connection only the first time the document is used in a session. The Set Connection command can be used to change the connection information associated with a document.
Execute SQL Command executes the SQL line containing the cursor. The scope of the line is determined by the TextMate grammar and may span several editor lines. If SQL commands are on consecutive lines, they are treated as a single scope and all SQL commands are executed. To ensure only a single command is executed, there must be a blank line following the command's trailing semicolon.
Execute Selection sends the selected text to psql for execution.
Execute File executes the entire file using psql. In this case, the optional search path for the connection string is not used. The search path must be set by a command in the the file.
The Drop command parses the current SQL line to formulate a drop statement for the current object. For example, if the cursor is in a CREATE FUNCTION line, the drop command generates a DROP FUNCTION statement with the appropriate parameters.
Indent or Complete allows the tab key to be used for both indentation and completion. Indentation is inserted if there is only whitespace to the left of the cursor. Otherwise, completion of the current word is attempted using tables, functions, and other objects from the current database.
Most of these commands simply jump to useful places in the PostgreSQL online documentation. The SQL Commands operation parses the current line and shows the appropriate SQL command documentation.
Copyright © 2010-2011 by John DeSoi, Ph.D.
All rights reserved.
Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.