PgAccess is a graphical administration application for PostgreSQL. It is designed to be
similar in function to PC database software, such as Microsoft Access.
Figure 6-1 displays the main PgAccess application window.
Figure 6-1. PgAccess application window
The interface allows you to view and modify various aspects of your PostgreSQL database using graphical representations
of database elements, such as tables, queries, and views (among others). It can be a convenient escape from the sometimes
tedious task of using the psql command line interface.
PgAccess was written in the Tcl/Tk scripting language; this increases its level of
portability, as it can be installed and run on any system that supports the Tcl/Tk scripting
language (including Linux/UNIX, Windows, and MacOS). As PgAccess is a client side
application, PostgreSQL is not required to be on the machine running PgAccess.
There are relatively few things you will need to do to configure PgAccess for use with
PostgreSQL. Most importantly, make sure that Tcl/Tk is installed and
configured properly.
To configure PostgreSQL with TCL support, you must have used the
--with-tcl flag during source
compilation. The use of the --with-tcl flag will configure the appropriate tcl libraries
for use with PostgreSQL. This flag will install the pgaccess binary for you.
Note: PgAccess will not operate unless you have configured PostgreSQL to support Tcl/Tk.
Linux distributions that come with PostgreSQL, such as Red Hat and Mandrake, should have TCL
support compiled in to their PostgreSQL binaries.
If you did not use the --with-tcl flag during your original compilation you can add TCL support
to your existing PostgreSQL configuration without having to reinitialize the PostgreSQL data directories by reconfiguring
PostgreSQL with the --with-tcl flag, and subsequently recompiling.
After the reconfiguration is complete, clean up the directory by typing: gmake clean, and then
recompile the code by typing: gmake. Finally, shut down postmaster and type the
command: gmake install. This will install the new binaries and libraries for the reconfigured
PostgreSQL system. Once these are installed you can safely restart PostgreSQL.
Warning
If you are going to recompile PostgreSQL after it has been installed, you must
use source from the same version of PostgreSQL that you originally compiled. If you use a different version of the
source, you may lose data. As always, it is a good idea to backup your data before performing any changes to
your PostgreSQL installation.
PgAccess provides the ability to graphically modify and manage user accounts associated with the database. Like
command-line clients, it uses CREATE USER and ALTER USER to accomplish these tasks.
The difference is that PgAccess provides a graphical front-end to these commands. This feature is available through the
Users tab on the left side of the PgAccess window. Figure 6-2 shows this tab.
Figure 6-2. The PgAccess Users tab
Clicking on Users will display a list of all users associated with the database. Of the
program's three action buttons, only New and Design perform actions from this
tab.
Clicking New will allow you to create a new user within the database. As you may notice, the options in this window
correlate with the options available through the use of the SQL command, CREATE USER. Use the
Username field to set the new user's username, and the Password and verify
password fields to set the password (if there will be one). The two check boxes set the
CREATEDB and CREATEUSER permissions for the new user, if checked (remember that
allowing these permissions creates the new user as a database superuser). You may use the Valid until
field to set the valid-until date for the user (the same as the CREATE USER command).
Clicking the Design button allows you to modify the attributes of the selected user account as
you would normally do with the SQL command, ALTER USER. As such, the options here
correlate with the options available through ALTER USER, such as modifying the
username, changing the password, setting the previously mentioned CREATEDB and
CREATEUSER permissions, and modifying the valid-until date.
As of Version 0.98.7, the most current version at the printing of this book,
PgAccess does not support the management of groups. You can use the command-line interface
psql to create and modify PostgreSQL user groups.
To create a database using PgAccess, click the Database menu option at the top of the screen, then click New and
type in the name you wish to give the database. This will create a database as if you had called the SQL
CREATE DATABASE command from the currently logged-in database user. To use a template
other than template1 for the new database or set its encoding type, you will need to
either use the createdb program, or the CREATE DATABASE command from a
command line client (see Chapter 9).
It is relatively easy to create and modify the tables of a database with PgAccess through its graphical interface.
Figure 6-3 shows this dialog window.
Figure 6-3. The PgAccess Create new table dialog box
To create a table, first click on the Tables tab, then click the New button to open the "Create new table" window.
This window contains various fields and buttons that allow you to modify the attributes of the table you are creating.
Basically, these commands are visual representations of the options found in the SQL CREATE TABLE
command.
The following list names available inputs within the PgAccess Create Table Dialog:
Table name
The name of the table you wish to create within PostgreSQL.
Inherits
You can set what table(s) from which this table will inherit attributes. A list of
available tables is located in the drop-down box. Note that the list is not restricted
to holding just one value; you can click the downward arrow button and choose another
table to add that table to the inheritance list.
Check
Enter any expressions you wish to have checked on INSERT and UPDATE
commands.
Constraint
Enter any constraints you wish to place upon the table.
The following subsections talk about how to add fields to a table and about how to insert and delete rows.
To add a field to the table, set its attributes with the field name,
type, size, and Default value fields, and set its options
with the "field cannot be null" and "primary key" check box options. Once you have
chosen the options, click the "Add field" button to add the field to the field list. You are able to
move fields up and down through the list with the "Move up" and "Move down"
buttons, delete a field with the "Delete field" button, and delete all fields with the
"Delete all" button.
Once you are ready to add the table into your database, click the Create button. The following
subsections discuss how to insert and delete of rows.
It is possible to insert values into a table using PgAccess; in fact, the process is fairly simple. Click on
the Table tab to view the list of tables, then click on the table you wish to modify and click
Open.
After clicking Open, you should see a window of rows and columns that contain the various
fields of your table. You can tab through these columns and rows to reach a target field, or just use your mouse to click on it.
The row chosen will become highlighted and a cursor will appear showing you the location of the new data. The database will not be
updated with your changes until you tab out of the field you were editing; or, alternatively, click into another
field with your mouse. PgAccess displays the message: "Updating database..." after you complete one of these
actions.
It may be useful to note that it is possible to both sort and filter the table data by making
use of two fields at the top of the table window, named logically "Sort field" and "Filter conditions." It is possible to
sort the table by a field or multiple fields by typing the name of the field into the "Sort field" box, optionally
including "ASC" or "DESC" if you wish for the sort to be ascending or descending, respectively. You can choose to sort by
multiple fields. To do this, include the names of other fields in a comma-delimited list. As an example, you could use the
following to sort a list of names by the lastname field, ascending:
lastname ASC
To use the "Filter conditions" box, enter filter conditions such as the following:
(age < 45) and (avgsalary > 40000)
The process for updating table data is the same as for inserting, but you change existing rows rather than adding
new ones.
To delete values from a table, open it in the same manner you would when attempting to insert values: click on the
Tables tab, then click on the table you wish to modify and click Open. Within
this window are the columns and rows of the table, filled with whatever data has been entered. You can either delete
rows, or specific fields within a row. To delete a row, click on the desired row, then hit the Delete key on your
keyboard. PgAccess will display a dialog box asking for confirmation of the delete, in case your choice to delete was
accidental. To delete the contents of a field, or the partial contents of a field, click or tab into that field and use the
Backspace key to delete characters.
As should be expected, you are able to design, edit, and run queries through PgAccess. Click on the
Queries tab to view a list of the defined queries associated with your database. This area of the
program should be familiar to Microsoft Access users, as the visual query designer and other features are very similar to
their counterparts within that program.
To create a new query, click the New button. This will
open the "Query builder" window. Before designing the query, you should name it with the Query name
field. This name is arbitrary and serves no function within the query; it is needed only so that PgAccess has something to
display for this query in the list of available queries. You may also add comments in the comment window at this point.
After naming the query, you can either design it manually or use the visual designer tool to speed up the process.
To manually design the query, use the large, white box below the Query name field to type in the
SELECT statement that will be used to query the database. You can spread this statement
out over multiple lines, if you wish.
To use the visual designer tool for creation of the new query, click on the "Visual designer"
button. As stated before, the interface to this tool is similar to the query designer tool in Microsoft Access. You are
initially given a blank canvas to work with. Add tables to the canvas by typing the name of the table in the
Add table field (the cursor enters text into this field by default). Alternatively, you can add
tables by clicking the down-arrow button and selecting the table you wish to add from its list of available
tables.
Once you've added the tables you wish to use, you can form links between them by clicking and dragging on a field,
then pulling it from one table object to the other. When a link is formed it will display as a thin line that connects
the two objects together. Note that you can move table objects around the canvas and the link graphic will
stretch to fit whatever arrangement you desire. You may delete tables from the canvas by clicking on their labels and
hitting the Delete key on your keyboard. Similarly, links may be deleted between columns by clicking on them and
pressing the Delete key.
Any links between corresponding table columns will be translated into a SQL WHERE
clause, specifying conditions upon which to join two table sets. A link will only represent a condition involving the
equal-to operator (=). If you require a different condition, the SQL statement can be
edited manually in the "Query builder" window; bear in mind that going back to the Visual Designer will cause any
modified relationship to be re-created as an equal-to relationship when it is saved.
Figure 6-4 shows the PgAccess Visual Designer interface. It illustrates a fairly
involved SQL query, reproduced in a more comprehensible, graphical form.
Figure 6-4. The PgAccess Visual query designer
To select fields that you wish to be included in the results of the query, drag the field name down into the
result zone (the cell-divided area at the bottom of the screen). You may define conditions you wish to be applied to
results from the query; do this by entering a condition into the Criteria field. To see the SQL
statement you have created with the visual design, click the "Show SQL" button. To execute your query (for testing
purposes), click the "Execute SQL" button. When you are done creating the query's design, click on the "Save to query
builder" button. This saves the query within the pga_queries table.
To view the results of an existing query, click the Queries tab, select the desired query from the list in the main
PgAccess window, and click the Open button. This displays the retrieved rows in a window similar to the window used
for modifying tables, though this table is read-only. You can use the Sort field to sort the
records by an expression, or the Filter conditions field to provide a filter expression.
To modify an existing query, click the "Queries" tab, select it from the query list in the main PgAccess window,
and click the Design button. This will display the "Query builder" window, which is the same view as if you were
to create a new query. The query's name and SQL statement will be displayed in the window, as well as any comments you
added onto it when it was originally designed. From here you can either edit the SQL statement directly or use the
visual designer.
Remember to click the "Save query definition" button to save your modifications to an existing query.
Creating functions within PgAccess is also fairly simple. First click on the Functions tab, then
click New. You should now be presented with the Function window. Here, you may enter the name of
your new function, the parameters it takes (comma-separated), the language it is written in (e.g.,
SQL, C, plpgsql, etc.), and the type of data it returns (if
the function returns a value). Once you have defined those options, enter the body of the function in the white box that
takes up most of the screen (or the location in the filesystem of the shared object file, if it is a C function). Once
finished, click Save.
Note: You may view existing function definitions by selecting one from the function list, and clicking
Open.
Figure 6-5 illustrates a simple example function, which selects the name of an author
based on the id value in the authors table.
Figure 6-5. The Function dialog box
We will continue the discussion about creating functions in the next chapter.
