<!-- begin of toc --> <h1 class="toc"><a href="#intro" target="_self">Introduction</a></h1> <h1 class="toc"><a href="#copyright" target="_self">Copyright</a></h1> <h1 class="toc"><a href="#user-interface" target="_self">1. User interface</a></h1> <h2 class="toc"><a href="#main-window" target="_self">1.1. The main window</a></h2> <h2 class="toc"><a href="#help" target="_self">1.2. On-line help</a></h2> <h2 class="toc"><a href="#context" target="_self">1.3. Context menu</a></h2> <h2 class="toc"><a href="#printing" target="_self">1.4. Printing</a></h2> <h2 class="toc"><a href="#shortcuts" target="_self">1.5. Keyboard operation</a></h2> <h2 class="toc"><a href="#license" target="_self">1.6. Displaying the license</a></h2> <h2 class="toc"><a href="#about" target="_self">1.7. About pfm</a></h2> <h1 class="toc"><a href="#database" target="_self">2. Working with data bases</a></h1> <h2 class="toc"><a href="#opendb" target="_self">2.1. Opening a data base</a></h2> <h2 class="toc"><a href="#check" target="_self">2.2. Checking the pfm_* tables</a></h2> <h2 class="toc"><a href="#closedb" target="_self">2.3. Close data base</a></h2> <h2 class="toc"><a href="#install" target="_self">2.4. Install pfm_* tables</a></h2> <h2 class="toc"><a href="#exampledb" target="_self">2.5. Install example database</a></h2> <h1 class="toc"><a href="#reports-queries" target="_self">3. Running SQL</a></h1> <h2 class="toc"><a href="#run-sql" target="_self">3.1. SQL terminal</a></h2> <h2 class="toc"><a href="#import-sql" target="_self">3.2. Import SQL-script</a></h2> <h1 class="toc"><a href="#options" target="_self">4. Configuring pfm</a></h1> <h1 class="toc"><a href="#forms" target="_self">5. Working with forms</a></h1> <h2 class="toc"><a href="#open-form" target="_self">5.1. Opening a form</a></h2> <h2 class="toc"><a href="#form-window" target="_self">5.2. The form window</a></h2> <h2 class="toc"><a href="#update" target="_self">5.3. Update record</a></h2> <h2 class="toc"><a href="#add" target="_self">5.4. Add record</a></h2> <h2 class="toc"><a href="#delete" target="_self">5.5. Delete record</a></h2> <h2 class="toc"><a href="#status" target="_self">5.6. Status field</a></h2> <h2 class="toc"><a href="#links" target="_self">5.7. Links to other tables</a></h2> <h2 class="toc"><a href="#fillingout" target="_self">5.8. Filling out a form</a></h2> <h2 class="toc"><a href="#views" target="_self">5.9. Views</a></h2> <h2 class="toc"><a href="#search-record" target="_self">5.10. Search record in internal buffer</a></h2> <h2 class="toc"><a href="#limit-clause" target="_self">5.11. Forms with a LIMIT-clause in their definition</a></h2> <h2 class="toc"><a href="#form-help" target="_self">5.12. Help for form</a></h2> <h1 class="toc"><a href="#definition" target="_self">6. The pfm_* tables</a></h1> <h2 class="toc"><a href="#pfm_form" target="_self">6.1. pfm_form</a></h2> <h2 class="toc"><a href="#pfm_attribute" target="_self">6.2. pfm_attribute</a></h2> <h2 class="toc"><a href="#pfm_value_list" target="_self">6.3. pfm_value_list</a></h2> <h2 class="toc"><a href="#pfm_value" target="_self">6.4. pfm_value</a></h2> <h2 class="toc"><a href="#pfm_link" target="_self">6.5. pfm_link</a></h2> <h2 class="toc"><a href="#pfm_report" target="_self">6.6. pfm_report</a></h2> <h2 class="toc"><a href="#pfm_section" target="_self">6.7. pfm_section</a></h2> <h2 class="toc"><a href="#schema" target="_self">6.8. The links between the pfm_* tables</a></h2> <h1 class="toc"><a href="#design" target="_self">7. Designing a form</a></h1> <h2 class="toc"><a href="#modify_form" target="_self">7.1. Modifying or adding a form</a></h2> <h2 class="toc"><a href="#define_attributes" target="_self">7.2. Defining a form's attributes</a></h2> <h2 class="toc"><a href="#define_links" target="_self">7.3. Defining links between forms</a></h2> <h2 class="toc"><a href="#define_value_lists" target="_self">7.4. Defining value lists</a></h2> <h1 class="toc"><a href="#reports" target="_self">8. Reports</a></h1> <h2 class="toc"><a href="#run-report" target="_self">8.1. Running a report</a></h2> <h2 class="toc"><a href="#design-data" target="_self">8.2. Designing the data for the report</a></h2> <h2 class="toc"><a href="#design-layout" target="_self">8.3. Designing the report layout</a></h2> <h1 class="toc"><a href="#PostgreSQL" target="_self">9. Hints for using PostgreSQL</a></h1> <h2 class="toc"><a href="#encoding" target="_self">9.1. Character encoding</a></h2> <h2 class="toc"><a href="#sql-hints" target="_self">9.2. Some general hints about SQL</a></h2> <h1 class="toc"><a href="#sample" target="_self">10. The example databases</a></h1> <h2 class="toc"><a href="#addressbook" target="_self">10.1. The addressbook database</a></h2> <h2 class="toc"><a href="#customerdb" target="_self">10.2. The customer database</a></h2> <!-- end of toc -->

Introduction

Postgres Forms (pfm) is a client application with a graphical user interface for the PostgreSQL data base server. It enables the user:

Postgres Forms is implemented in Tcl/Tk, but there is no need for the user to program anything in Tcl/Tk. The user has to use SQL for creating tables and views and for designing forms and links.

Postgres Forms makes no attempt to hide the underlying SQL. On the contrary, in most cases, it shows both the SQL statements it sends to the PostgreSQL server and the results it gets back.

Postgres Forms has been designed and tested with version 8.5 of Tcl/Tk and with version 8.1 of the PostgreSQL database server. It has also been tested with version 9.0 of PostgreSQL. For more information on Tcl/Tk and PostgreSQL look at http://www.tcl.tk and http://www.postgresql.org respectively.

The pfm distribution includes a tclkit 8.5.9 for the following architectures:

For these architectures there is no need for the user to install Tcl/Tk separately.

Postgres Forms needs one of the Tcl interface packages pgtcl, pgtclng or pgintcl to communicate with PostgreSQL database server. All three can be obtained from http://pgfoundry.org.

pgintcl-3.2.1 is included in the pfm distribution.

Copyright

Postgres Forms (pfm) is a client application for PostgreSQL.

Copyright © 2004-2011 Willem Herremans

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

A copy of the GNU General Public License can be displayed with the menu item 'License' under 'Help'.

The home page of pfm can be found at

http://pgfoundry.org/projects/pfm

There you can report bugs, request new features and get support.

1. User interface

1.1. The main window

The following figure shows the main window of Postgres Forms (pfm):

Main window

This is a tabbed window with 3 tabs:

1.2. On-line help

You can invoke the on-line help by selecting "Help file" in the "Help" menu on the main window.

Pressing F1 on the main window has the same effect.

The help file is displayed in the web browser of your choice. See 4. Configuring pfm for how to choose a web browser.

1.3. Context menu

By clicking the right mouse button the "context menu" pops up.

It is a classic "edit menu" with menu items: Copy, Cut and Paste.

You can use it to copy text to/from the clipboard.

This menu is not restricted to the main window, but works on all the windows of pfm.

1.4. Printing

Printing can be invoked from the 'Run SQL' window for printing the text in the output text area (menu Output -> Print) or from any 'text window' (menu Text -> Print).

How printing is handled completely depends on the value of the 'printcmd' option. See help for option 'printcmd': invoke menu 'Tools -> Options' then press 'Help' button of option 'printcmd'.

The 'printcmd' calls an application that accepts plain text as input. It can be either a printer program that directly sends the text to a printer, or a text editor or word processor that accepts the text generated by pfm as input and that offers you the possibility to choose things such as font, font size, margins, page orientation etc. before sending it to a printer from there on.

The command may contain parameters for which you will be prompted to provide a value when the command is called.

1.5. Keyboard operation

It is possible to operate pfm without using the mouse. Not all keyboard procedures are explicitly mentioned in this manual because they follow some general rules which make it very easy to know them.

  1. The menus on the menu bar of any window have one underlined character. Pressing "Alt-<that character>" invokes the menu.
  2. The menu items can also have an underlined character. Pressing that character when the menu is displayed, selects the corresponding menu item.
  3. The menu items can have a so called "accelerator" or "shortcut" displayed in the second column of the menu. Pressing that key causes the menu item to be invoked even when the menu is not displayed.
  4. There can be several widgets like text entries, buttons and listboxes on a window that can receive keyboard input. At any time, there is only one widget that receives the keyboard input. That widget has the so called "input focus". The widget that has input focus is shown with a black frame border.

    Clicking with the mouse on a widget moves the input focus to that widget. The widgets on a window that can receive input focus are arranged in a circular list. Pressing the Tab key moves the input focus forward along the list. Pressing Shift-Tab moves the input focus backward along the list.

  5. Buttons can have an underlined character. Pressing "Alt-<that character>" has the same effect as pressing that button.
  6. Pressing Return on when a button has the input focus, has the same effect as clicking on the button with the left mouse button.
  7. The Escape key usually has the effect of closing the window which has input focus without doing anything else. The only exceptions are:

1.6. Displaying the license

Selecting "License" in the "Help" menu on the main window causes the "GNU General Public License" to be displayed.

1.7. About pfm

Selecting "About" in the "Help" menu on the main window displays some general information about postgres forms, such as: version, copyright, contact information, Tcl/Tk run-time environment, interface to PostgreSQL, installation directory.

2. Working with data bases

2.1. Opening a data base

After selecting 'Open...', from the Database menu on the main window, you are prompted to enter the parameters for opening a data base:

Notes:

  1. When a database is opened, pfm is connected twice with the PostgreSQL database server:
    • via one of the Tcl interfaces Pgtcl or pgin.tcl for all form and report operations;
    • via psql for the "Run SQL" feature.
  2. You are only prompted for a password if the "usePGPASSWORD" option is 'on'. In that case, the password you enter is stored in the environment variable PGPASSWORD during the short time between pressing 'OK' on the Open database window and the actual connecting of psql with the database server. For connecting via the Tcl interface Pgtcl or pgin.tcl, the connection parameter "password" is used instead of the environment variable.
  3. If the "usePGPASSWORD" option is 'off', you are not prompted for a password and the environment variable PGPASSWORD is not used. In that case you need a properly configured password file. See subsection "The password file" in the "libpq C-library" chapter of the PostgreSQL documentation. On UNIX platforms the password file is

    ~/.pgpass;

    on Windows platforms the password file is

    APPDATA\postgresql\pgpass.conf

    where APPDATA is the user's application data directory, e.g.

    C:\Documents and Settings\'username'\Application Data

  4. If the "usePGPASSWORD" option is 'off', pfm reads the password file to find the password, which is then passed to the database server via the "password" connection parameter, when connecting via the Tcl interface Pgtcl or pgin.tcl. pfm supports the "\:" and "\\" sequences to escape ":" and "\". For connecting to the database server via psql, psql reads the password file independently from pfm.
  5. According to the PostgreSQL documentation, the use of the environment variable PGPASSWORD is deprecated for security reasons.

Default values for the parameters host, port, user and dbname are read from the user's options file (see section 4. Configuring pfm).

All parameters can be typed directly on the window that is displayed. The 'dbname' can also be selected from a list 'dblist' defined in the user's options file.

After a database has been successfully opened, its name is stored in the 'dbname' option and added to the 'dblist' option if it is not in the list yet.

2.2. Checking the pfm_* tables

When you open a database, pfm checks for the presence of the pfm_* tables.

If pfm does not find the pfm_* tables, you get a hint that you can install them using the 'Tools -> Install pfm_* tables' or the 'Tools -> Install example database ...' menus.

On a database without pfm_* tables, pfm only allows you to execute SQL statements using the RunSQL feature.

On a database with pfm_* tables, pfm allows you to also design and use forms and reports.

When you open a database that was previously managed by an older version of pfm, you will be asked whether you want to convert the pfm_* tables to the new format.

If you choose "yes", the pfm_* tables are converted to the format compatible with the new version of pfm.

Note:

There is no way back: i.e. there is no automatic conversion from the new to the old format. So, to be sure, it is recommended to first make a backup of your database using PostgreSQL's "pg_dump" before answering "yes" to this question.

If you choose "no", the database is opened but not modified in any way. Since the format of the pfm_tables is not compatible with the new version of pfm, some features like defining and using forms and reports may not work properly, but you will be able to execute SQL statements using the RunSQL feature of pfm.

2.3. Close data base

By selecting the Close menu item from the Database menu on the main window, you close the currently open data base. The list-boxes on the main window become empty.

2.4. Install pfm_* tables

To install the pfm_* tables in the currently opened database, select 'install pfm_* tables' from the Tools menu on the main window.

2.5. Install example database

There are two example databases included in the pfm package:

Using 'Tools -> Install example database', you can install one of these example databases in the currently opened database. This database should be completely empty. It should not even contain the pfm_* tables.

The easiest way to obtain a completely empty database is to create a new one:

See section 10. The example databases for more details on the structure of the example databases.

The example databases only contain characters of the LATIN1 character set. So, LATIN1 encoding is sufficient. UNICODE is more than sufficient. See section 9.2. Character encoding for more details on character encoding.

3. Running SQL

3.1. SQL terminal

When you invoke the menu SQL on the main window's menu bar, the following window is opened:

SQL window

It has two text areas, one for the SQL statements, another for the output.

This tool is in fact nothing more than a GUI-frontend for the psql interactive terminal that is included in PostgreSQL.

When you press the [Run] button, all the text present in the SQL statement text area is sent to psql.

Don't forget to end SQL statements with ";". If you forget this, the statement is not executed by psql until you type ";" and press [Run].

Sometimes, the Run SQL feature appears to be dead, i.e. it is not responding to commands anymore. In many cases that is due to mismatched parentheses or quotes. E.g. if you enter

SELECT * FROM pfm_form WHERE (name='pfm_attribute';

psql does not respond until it gets a ')'. If you run psql in an xterm, it displays a prompt ending with '(>', indicating that there is still an unmatched '('. In pfm however, psql does not display this prompt, with the result that it does not seem to respond. If you cannot guess what is wrong, you can close and reopen the database to recover from this frustrating situation.

Apart from SQL statements, you can also enter the psql "\" commands. E.g.

These help functions can also be invoked from the Help menu on the "Run SQL" window.

Every time you press [Run] the commands in the SQL area are stored in the command history. You can scroll through the command history using the [Previous] end [Next] buttons.

Note:

Alt-p and Cntrl-Up are alternatives for the [Previous] button.

Alt-n and Cntrl-Down are alternatives for the [Next] button.

Since version 2.0.0, you need not press the Run button if you end an SQL statement with ';<Return>'. Similarly, if the command you are typing begins with a '\', the command is sent to psql when you press '<Return>'.

The output area is not cleared when another run is done, i.e. the output of a new run is appended to the text already present in the output area. You can clear the output area explicitly by invoking the menu Output -> Clear.

3.2. Import SQL-script

You can also import SQL statements from a file by selecting "Import SQL from file" in the SQL menu on the "Run SQL" window.

After having selected the file to be imported, you are asked to select the character encoding. The default is the same as your system's default encoding. Usually, that is OK, but if you know that the file to be imported was made with another character encoding, you can select it here.

You are also asked whether to offer the file to psql via the '\i' command or to import the file into the SQL window. For large files it is recommended to offer the file to psql. pfm sometimes hangs for no obvious reason when large files are run from the SQL window.

Notes:

The text in the output area can be printed or saved to a file, by selecting respectively "Print" or "Save" in the "Output" menu on the "Run SQL" window.

4. Configuring pfm

Postgres Forms has a number of options that are stored in a text file. On UNIX platforms that file is

~/.pfm2

On Windows platforms that file is

APPDATA\pfm\pfm2.conf

where APPDATA is the user's application dirctory, e.g.

C:\Documents and Settings\'username'\Application Data

These options can be viewed and modified by selecting the Tools -> Options menu item:

Options window

For each option you can get dedicated on-line help by clicking the Help button at the right.

If you start Postgres Forms for the first time, the options have their default values. On later occasions, the options are read from the options file.

5. Working with forms

5.1. Opening a form

When a form is opened, by pressing the [Open] button or the Return key on the main window, you will see a window such as this:

Open form window

This window shows the SQL SELECT statement that will be executed to load the data into the form.

You can modify the 'WHERE' and 'ORDER BY' clauses.

The 'paste attribute Name' and 'past attribute Value' menus may help you to do so.

After pressing the 'Run' button, the SQL SELECT statement is executed, the form is displayed in a new tab of the current window and the data resulting from the SQL SELECT statement are loaded into the form's internal buffer.

5.2. The form window

The form window looks like this:

Form window

The upper text field shows some information on how the form was opened. This will become more interesting when you have followed one or more links.

The lower text field displays the SQL statements that were sent to the PostgreSQL server. On this example, it shows the SQL statement that was issued to load the data in to the form's internal buffer.

The middle part of the window is the actual form. It shows:

The right side of the form window shows the link buttons which can be used to follow 'one-to-many' or 'many-to-one' relationships to other forms. See 5.7. Links to other tables for more details.

5.3. Update record

After you press the [Update] button, Postgres Forms reloads the current record.

If that is successful, the form's table attributes become modifiable, the buttons [OK] and [Cancel] are displayed and all the other buttons on the form are deactivated. The form's status becomes "Updating".

The calculated fields and the attributes of tables other than the form's main table are read-only and do not become modifiable.

If you press [OK]:

Note:

If a LIMIT clause has been specified in the definition of the form, the behaviour is slightly different from the one described above. See 5.11. Forms with a LIMIT-clause in their definition for details.

If you press [Cancel], the modifications are discarded.

While a record is displayed on your screen, it may be deleted by another user. This can happen :

In the first case, this is noticed by Postgres Forms when you press the [Update] button. In the second case it is noticed when you press the [OK] button. In both cases, the record is marked as deleted and the update operation is cancelled.

Similarly, while a record is displayed on your screen, it may be modified by another user. This can happen:

In the first case, the 'reload record', which is executed when you press [Update], refreshes the screen and the update proceeds as described above.

In the second case, Postgres Forms notices that the record has been modified by another user when you press [OK]. Then, the update operation is cancelled, and you are notified.

5.4. Add record

After you have pressed the [Add] button, the displayed record's attributes become modifiable in the same way as for "update record". The attributes for which a default value has been defined in pfm_attribute now get their default value on the screen. The buttons [OK] and [Cancel] are displayed and all the other buttons on the form are deactivated. The form's status becomes "Adding".

If you press [OK], the new record is stored, both in the data base and in the form's internal buffer. In the form's internal buffer, it becomes the last record. This means that even if the new record's values are such that the record would not be selected by original query, it stays in the internal buffer until the form is closed.

Similarly, the record gets the last position in the internal buffer, even if the that is not the position it would get according to the initial "ORDER BY" clause.

If you press [Cancel], the new record is discarded, i.e. it is neither stored in the data base, nor in the form's internal buffer.

After pressing [OK]:

Note:

If a LIMIT clause has been specified in the definition of the form, the behaviour is slightly different from the one described above. See 5.11. Forms with a LIMIT-clause in their definition for details.

5.5. Delete record

If you press the [Delete] button, the current record is deleted from the data base. It is not deleted from the internal buffer, but its attributes are all put to an empty string and its status field is put to "Deleted".

Note:

If a LIMIT clause has been specified in the definition of the form, the behaviour is slightly different from the one described above. See 5.11. Forms with a LIMIT-clause in their definition for details.

5.6. Status field

The status field is a property of a record in the form's internal buffer. It is kept in the internal buffer until the form is closed and it is displayed on the form. The possible values are:

Whenever there is a one-to-many relationship between two database tables, you can define links between the corresponding forms.

When a form is displayed, all the links originating from that form, are displayed as buttons in the 'links' pane.

So, links are navigation tools that allow you to follow the one-to-many relationships in a database.

Note that you can define a link in the "one-to-many" direction and another one in the "many-to-one" direction of a relationship.

Pressing a link button, brings you to another form in which the records, related to the originally displayed record, are loaded.

This form is opened in a new tab of the current window. By switching tabs you can move back to the form and record from which the link has originated.

Every time you follow a link, another tab is created. So, after having followed one or more links, you get a chain of records in which each record is represented by a tab on the window.

All tabs in the chain, except the last one, are 'locked'. That means that no operations are allowed on them.

You can 'unlock' a 'locked tab', by pressing the 'unlock tab' button or the 'Escape' key. That also deletes all tabs to the right of it.

Links are defined in the pfm_link table. See 6.5. pfm_link for details.

Note:

Altough "many-to-many" relationships do exist in the real world, they are implemented as two "one-to-many" relationships, using an auxilliary table.

E.g. if you have a database containing tables for persons and groups of persons, you can have a many-to-many relationship between persons and groups: a person can be member of more than one group, and a group can contain more than one person.

To implement such a database, you would not only need tables "person" and "group", but also a table "membership". The "many-to-many" relationship between "person" and "group" is then implemented as a "one-to-many" relationship between "person" and "membership" and another "one-to-many" relationship between "group" and "membership".

So, to find all the members of a group, you first follow the one-to-many relationship to the membership table, and from there the many-to-one relationship to the person table.

5.8. Filling out a form

After pressing [Update] or [Add], you can fill out a form, i.e. provide values for the table's attributes.

You can provide values for the attributes in one of the following ways, depending on the attribute's "type of get" (see section 6.2. pfm_attribute):

Notes:

5.9. Views

For a "view" it is possible to define a form in Postgres Forms in the same way as for a "table", but it is not possible to update a view.

The buttons [Update], [Add] and [Delete] are absent on a form for a view.

5.10. Search record in internal buffer

The "Search" menu on the "Form" window contains a list of the form's attributes. After having selected an attribute, you get a search bar in the form window in which you can specify a value for that attribute.

You can then search the internal buffer for the next record with the specified value for that attribute.

5.11. Forms with a LIMIT-clause in their definition

The designer of the form can specify a LIMIT-clause to limit the number of records that is loaded into the form's internal buffer. This makes it possible to handle large tables without causing excessive memory usage. It is almost, but not entirely transparent for the user of the form.

As an example, we consider a form for which 'sqllimit = 100' has been specified in pfm_form and for which there are 523 records in the database.

When the form is opened, not all 523, but only the first 100 records are loaded into the internal buffer. Since pfm does not know how many records have not been loaded, it displays the the record number for records as '1/?', '2/?', ... '100/?'.

When you move beyond record 100, the internal buffer is first cleared and then reloaded with the next 100 records.

The same thing happens again when you move beyond records 200, 300 etc...

Finally, when moving beyond record 500, pfm only finds 23 instead of 100 records. Then it assumes that 523 is the last record and displays the record numbers as '501/523', '502/523', ... '523/523'.

When moving backwards through the records in the internal buffer, e.g. when moving from record 501 to record 500, the internal buffer is first cleared and then reloaded with the records 401 through 500.

Also searching the internal buffer with the Search menu, causes pfm to load another set of 100 records when required.

There are a few cases where this automatic clearing and reloading of the internal buffer has some confusing effects.

5.12. Help for form

By invoking the "Help" menu on the "Form" window you get help information for filling out the currently displayed form.

This is the text that is stored in the "help" attribute of the table "pfm_form".

For the pfm_* forms, this help text is provided by pfm, but for the other forms that information has to be provided by the designer (probably you) of the form. See also section 6.1. pfm_form

6. The pfm_* tables

Per data base, pfm uses tables with a name starting with "pfm_" to store the definition of forms and reports.

6.1. pfm_form

A form allows the user to administer the data of just one table. This table is henceforth referred to as "the form's main table".

However, a form also has an SQL SELECT statement, which generates the data that are displayed on it.

In the simplest case the SQL SELECT statement is just:

SELECT <attributes of main table> FROM <main table>

In that case, the data which can be administered and the data which are displayed on the form are the same.

In more complex cases, the <main table> can be JOINED with other tables, which makes it possible to display data of other related tables as well. These data cannot be modified by means of the form.

The table "pfm_form" has the following attributes:

The form's main table is defined by tablename. Only the data of that table can be administered by using the form.

All the data generated by the form's SQL SELECT statement can be displayed on the form. The SQL SELECT statement is defined by:

Note:

The WHERE clause provided by the user when opening the form, becomes a HAVING clause, if there is a GROUP BY clause.

The following rules should be observed when filling out sqlselect and sqlfrom:

  1. The form's main table must appear in 'sqlfrom', and must not be aliased. Similarly, the main table's attributes appearing in 'sqlselect' must not be aliased. The other tables appearing in the 'sqlfrom' may be aliased.
  2. The fields appearing in 'sqlselect' must have a unique, simple name without the need to precede them with a tablename. So, calculated fields must be given a name by aliasing and attributes of tables other than the main table may need to be aliased in order to have a unique, simple name.
  3. The 'sqlfrom' is either just the name of the form's main table, or it is a JOIN clause in which 1 of the tables is the form's main table. Several join clauses can be nested in order to involve more than 2 tables. See examples below.

Example 1: the SQL SELECT for the person form of the addressbook database

tablename:
    person

pkey:
    id

sqlselect:
    id, christian_name, name, street, town, "ZIPcode",
    country, category, description

sqlfrom:
    person

groupby:
    -

Example 2: the SQL SELECT for the memberlist form of the addressbook database

tablename:
    memberlist

pkey:
    group person

sqlselect:
    memberlist."group", memberlist.person, p.christian_name, p.name

sqlfrom:
    memberlist LEFT OUTER JOIN person p ON (p.id = memberlist.person)

groupby:
    -

6.2. pfm_attribute

The table "pfm_attribute" defines all the properties of form attributes.

It has the following attributes:

6.3. pfm_value_list

The table "pfm_value_list" contains all the value lists of all the forms.

Its only attribute is

6.4. pfm_value

The table "pfm_value" contains all the values of the lists defined in pfm_value_list.

It has the following attributes:

A link is a navigation tool which allows you to follow a "one-to-many" or "many-to-one" relationship from one form to another.

Every link is stored as a record in the pfm_link table, which has the following attributes:

Note:

Postgres Forms does not provide any checks to safeguard the referential integrity of the data base in case of updates or deletions. However, PostgreSQL provides these functions as 'foreign key' table constraints (see PostgreSQL documentation).

6.6. pfm_report

The table pfm_report defines all the reports for the current data base.

pfm_report has the following attributes:

The sqlselect may contain one or more parameters for which a value is requested at "Run report" time. A parameter in the sqlwhere must be formatted as $(parameter_name).

Example:

sqlselect:

    SELECT g.name AS "group", g.description, p.id, p.name,
           p.christian_name, p.street, p."ZIPcode", p.town, p.country
    FROM "group" g
       LEFT JOIN memberlist m ON g.name = m."group"
       LEFT JOIN person p ON m.person = p.id
    WHERE "group" = '$(group)'
    ORDER BY g.name, p.name, p.christian_name

When the report is run, the user is prompted to enter a value for the parameter "group". Then the report data are generated by executing the sqlselect statement in which $(group) is replaced with the value entered by the user.

6.7. pfm_section

The data returned by the report's SQL SELECT statement may be considered as a table with a column for each 'field' specified after the word 'SELECT' and with a row for each record.

By specifying an 'ORDER BY' clause in the report's SQL SELECT statement, it is possible to group rows with the same values for some fields together.

The report generator has an "economy" algorithm which avoids printing the same data repeatedly.

To control this you have to distribute the fields (columns) of the table over n sections such that section 1 contains the fields that are changing least frequently (when moving from one row to the next), section 2 contains the fields that are changing more frequently, and section n contains the fields that are changing at every row.

When the data of the first row of the table are printed, the data of section 1 are printed first. Then, on the following line, indented by one tab stop, the data of section 2 are printed. Then, on the following line, indented by 2 tab stops, data of section 2 are printed, etc.

[section 1] <--- row 1

    [section 2] <--- row 1

        [section 3]  <--- row 1

Then, when the next rows are being printed, data of the lower numbered sections are only printed if they are different from the data of the last printed section of the same number:

[section 1]

    [section 2]

        [section 3]  <--- row 1
        [section 3]  <--- row 2
        [section 3]  <--- row 3

    [section 2]

        [section 3]  <--- row 4
        [section 3]  <--- row 5

[section 1]

    [section 2]

        [section 3]  <--- row 6
        [section 3]  <--- row 7

The report generator also enables you to print a summary at every point where a higher numbered section is about to be followed by a lower numbered section:

[section 1]

    [section 2]

        [section 3]  <--- row 1
        [section 3]  <--- row 2
        [section 3]  <--- row 3

        [summary 3]

    [section 2]

        [section 3]  <--- row 4
        [section 3]  <--- row 5

        [summary 3]

    [summary 2]

[section 1]

    [section 2]

        [section 3]  <--- row 6
        [section 3]  <--- row 7

        [summary 3]

    [summary 2]

[summary 1]

A summary i is printed just before a lower numbered section j (j < i). Its data can be calculated:

In particular, summary 1 is printed at the end of the report, is calculated from all the sections of the report and may be calculated from all the fields.

A record in pfm_section defines a section and a summary of a report.

The table pfm_section has the following attributes:

The fieldlist is a space separated list of field specifiers:

    field_spec_1 field_spec_2 ... field_spec_N

where each field specifier is formatted as follows:

    {field_i label_i alignment_i max_length_i}

where :

For every section, the layout can be defined as:

The summary must be formatted as a space separated list of summary specifiers:

    summary_spec_1 summary_sepc_2 .... summary_sepc_N

where each summary_spec is formatted as follows:

    {field_i aggregate_i format_i}

where:

Aggregate functions:

In general, the aggregate functions, use the same "economy" algorithm that is used for printing section data.

When all the fields of a section, which is not the highest numbered section of the report, have the same values for a number of consecutive rows, this section's data are only printed once for these rows.

Similarly, these rows are only counted once by the aggregate functions applied to a field of this section.

The aggregate functions that can be used in a summary are:

'ANSI C sprintf' formatting string:

Here is a short overview of the 'ANSI C sprintf' formatting string. In general its form is:

%'MinWidth'.'Precision''Conversion'

where:

6.8. The links between the pfm_* tables

The following drawing shows the links between the pfm_* tables:

Schema of pfm tables

The referential integrity is guaranteed by the following table constraints which are introduced when pfm installs the pfm_* tables:

ALTER TABLE pfm_attribute ADD CONSTRAINT ref_form
FOREIGN KEY (form) REFERENCES pfm_form (name)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE pfm_link ADD CONSTRAINT ref_fromform
FOREIGN KEY (fromform) REFERENCES pfm_form (name)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE pfm_link ADD CONSTRAINT ref_toform
FOREIGN KEY (toform) REFERENCES pfm_form (name)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE pfm_value ADD CONSTRAINT ref_list
FOREIGN KEY (valuelist) REFERENCES pfm_value_list (name)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE pfm_attribute ADD CONSTRAINT ref_value_list
FOREIGN KEY (valuelist) REFERENCES pfm_value_list (name)
ON DELETE RESTRICT ON UPDATE CASCADE;

7. Designing a form

Designing a form is in fact nothing more than filling out the data base tables pfm_forms, pfm_attribute, pfm_value_list, pfm_value and pfm_link defined in section 6. The pfm_* tables.

This is done using the forms that are predefined for these tables.

Note:

Postgres Forms does not offer the possibility to define data base tables or to modify their definition. That can be done using SQL statements CREATE TABLE ... or ALTER TABLE .... using the "Run SQL" function of Postgres Forms (see 3.1. Run SQL) or from 'psql', the interactive SQL terminal of PostgreSQL.

7.1. Modifying or adding a form

To modify or add a form:

7.2. Defining a form's attributes

First proceed as explained in the previous section to make the form you want to work on visible in the pfm_form form.

Then, follow the 'Attributes' link by pressing the [Attributes] button. This results in the form pfm_attribute being opened with all the attributes of the selected form loaded in the internal record buffer.

Here you can add, delete or modify the form's attribute definitions as appropriate. Note, that it is possible for the form to have less attributes than the table corresponding to the form, but all the attributes defined in the form must be returned by the SQL SELECT statement related to the form.

See section 6.2. pfm_attribute for a description of the attributes of pfm_attribute.

It may be necessary to add a value list for some attributes. See section 7.4. Defining value lists.

When finished, press [Back] to return to the form defining the selected form.

First proceed as explained in the section 7.1. Modifying or adding a form to make the form you want to work on visible in the pfm_form form.

To define links originating from the selected form, press the [Originating links] button. Then add, modify, delete links as appropriate. See section 6.5. pfm_link for a description of the attributes of pfm_link.

When finished, press [Back] to return to the form defining the selected form.

To define links terminating on the selected form, press the [Terminating links] button. Then add, modify, delete links as appropriate.

When finished, press [Back] to return to the form defining the selected form.

Links allow you to jump from one table/form to another while taking into account the relationships between the tables.

7.4. Defining value lists

To modify an existing value list, select the form 'pfm_value_list' in the main window and press the [open] button.

Then, specify a "WHERE" clause to select that value list and press the [Run] button. This results in the selected value list to be displayed. Here you can modify it's name or delete it.

To add, modify or delete the list's values, press the [Values] button. This opens the pfm_value form with all the list's values in Postgres Form's internal buffer. Here, you can modify, add, delete values.

To define a new value list, select the form 'pfm_value_list' in the main window and press the [open] button.

Then, don't specify a "WHERE"-clause, press [Add] and specify the name of the new value list.

To insert values for the list, press the [Values] link-button.

8. Reports

8.1. Running a report

To run a report, select the 'Reports' tab on the main window, select a report on the list-box and press the [Run] button.

The output appears in a new window.

For some reports you may be prompted to specify a value for some parameters.

8.2. Designing the data for the report

The first thing you need to design a report is an SQL SELECT statement which generates the data you need for the report.

You can use the "Run SQL" function of Postgres Forms to design this SQL SELECT statement.

Let us take an example from the addressbook sample database (see 10.1. The addressbook sample database for more information).

Let us assume that we want to list all groups and the persons that are member of these groups. The following SELECT statement generates the "raw" data that we need for that report:

SELECT g.name AS "group", g.description, p.id, p.name, p.christian_name,
       p.street, p."ZIPcode", p.town, p.country
FROM "group" g
     LEFT JOIN memberlist m ON g.name = m."group"
     LEFT JOIN person p ON m.person = p.id
ORDER BY g.name, p.name, p.christian_name

The result is:

  group  |             description             | id |     name     | christian_name |        street        | ZIPcode |   town    | country
---------+-------------------------------------+----+--------------+----------------+----------------------+---------+-----------+---------
 cycling | Cycling companions                  | 10 | Van Horebeke | Norbert        | Hoogstraat 3         | 9620    | Zottegem  | Belgium
 family  | Members of the family               |  8 | Lemmens      | Nancy          | Copernicuslaan 198   | 9000    | Gent      | Belgium
 family  | Members of the family               | 10 | Van Horebeke | Norbert        | Hoogstraat 3         | 9620    | Zottegem  | Belgium
 family  | Members of the family               |  6 | Van Riel     | Hugo           | Kerkstraat 56        | 2520    | Ranst     | Belgium
 pfm     | Persons involved in the pfm project |  1 | Brouwers     | Adriaan        | De Coninckstraat 23  | 8750    | Zwevezele | Belgium
 pfm     | Persons involved in the pfm project |  2 | Van de Perre | Albert         | Schanslaan 45        | 2600    | Berchem   | Belgium
 tennis  | Acquaintances from the tennis club  | 10 | Van Horebeke | Norbert        | Hoogstraat 3         | 9620    | Zottegem  | Belgium
 tennis  | Acquaintances from the tennis club  |  6 | Van Riel     | Hugo           | Kerkstraat 56        | 2520    | Ranst     | Belgium
 tennis  | Acquaintances from the tennis club  |  2 | Van de Perre | Albert         | Schanslaan 45        | 2600    | Berchem   | Belgium
 tennis  | Acquaintances from the tennis club  |  3 | Verdonck     | Nelly          | Azalealaan 33        | 9080    | Lochristi | Belgium
 work    | Acquaintances from work             |  1 | Brouwers     | Adriaan        | De Coninckstraat 23  | 8750    | Zwevezele | Belgium
 work    | Acquaintances from work             |  8 | Lemmens      | Nancy          | Copernicuslaan 198   | 9000    | Gent      | Belgium
 work    | Acquaintances from work             | 12 | Van Geluwe   | Adri           | Jan Breydelstraat 21 | 8511    | Aalbeke   | Belgium
 work    | Acquaintances from work             |  3 | Verdonck     | Nelly          | Azalealaan 33        | 9080    | Lochristi | Belgium
(14 rows)

Postgres Forms has tools to easily layout a report for these data. That is explained in the next section.

8.3. Designing the report layout

Enter the design mode by selecting the 'Design' tab on the main window.

Then select the form pfm_report and open it.

Don't specify a "WHERE" clause, press [Run] and then [Add].

Then fill out the form as follows:

Then press the [Sections] link button to define the sections and their layout.

A group having more than 1 member is listed more than once in the "raw" output of the report's SELECT statement.

Because of the order specified in the SELECT statement, all records related to a group are grouped together.

By defining a report with 2 sections (levels), we can avoid printing the data for a group more than once.

Section 1 will only print the fields related to group.

Section 2 will only print the fields related to a person.

When the report is printed, the records are printed in the order specified by the SELECT statement.

When the first record is printed, first section 1 data are printed, then section 2 data.

For every next record, the section 1 data are only printed if they differ from the data of the previously printed section 1 data. Then section 2 data are printed.

In this example, section 1 is a so called "group level" section.

In general, all sections, except the highest numbered section, are "group level" sections.

Possible section definitions for our example are shown below:

    level     : 1
    layout    : row
    fieldlist : {group group l} {description description l}
    summary   : {group COUNT}

    level     : 2
    layout    : table
    fieldlist : {id id r} {christian_name "Chr. name" l} {name name l}
                {street street l} {ZIPcode ZIP l} {town town l}
                {country country l}
    summary   : {id COUNT}

See 6.7. pfm_section for more details on section definitions.

When this report is run (Tab 'Reports' on main window, Select "Persons and groups", press [Run]), this is the result:

Groups and persons
------------------

Description: Lists all groups and their members
SQL        : SELECT g.name AS "group", g.description, p.id, p.name, p.christian_name,
                    p.street, p."ZIPcode", p.town, p.country
             FROM "group" g
                LEFT JOIN memberlist m ON g.name = m."group"
                LEFT JOIN person p ON m.person = p.id
             ORDER BY g.name, p.name, p.christian_name
Date       : 13-Dec-2004


group: cycling; description: Cycling companions;

     id | Chr. name | name         | street               | ZIP  | town      | country
    ----+-----------+--------------+----------------------+------+-----------+---------
     10 | Norbert   | Van Horebeke | Hoogstraat 3         | 9620 | Zottegem  | Belgium

    Summary: COUNT(id) = 1

group: family; description: Members of the family;

     id | Chr. name | name         | street               | ZIP  | town      | country
    ----+-----------+--------------+----------------------+------+-----------+---------
      8 | Nancy     | Lemmens      | Copernicuslaan 198   | 9000 | Gent      | Belgium
     10 | Norbert   | Van Horebeke | Hoogstraat 3         | 9620 | Zottegem  | Belgium
      6 | Hugo      | Van Riel     | Kerkstraat 56        | 2520 | Ranst     | Belgium

    Summary: COUNT(id) = 3

group: pfm; description: Persons involved in the pfm project;

     id | Chr. name | name         | street               | ZIP  | town      | country
    ----+-----------+--------------+----------------------+------+-----------+---------
      1 | Adriaan   | Brouwers     | De Coninckstraat 23  | 8750 | Zwevezele | Belgium
      2 | Albert    | Van de Perre | Schanslaan 45        | 2600 | Berchem   | Belgium

    Summary: COUNT(id) = 2

group: tennis; description: Acquaintances from the tennis club;

     id | Chr. name | name         | street               | ZIP  | town      | country
    ----+-----------+--------------+----------------------+------+-----------+---------
     10 | Norbert   | Van Horebeke | Hoogstraat 3         | 9620 | Zottegem  | Belgium
      6 | Hugo      | Van Riel     | Kerkstraat 56        | 2520 | Ranst     | Belgium
      2 | Albert    | Van de Perre | Schanslaan 45        | 2600 | Berchem   | Belgium
      3 | Nelly     | Verdonck     | Azalealaan 33        | 9080 | Lochristi | Belgium

    Summary: COUNT(id) = 4

group: work; description: Acquaintances from work;

     id | Chr. name | name         | street               | ZIP  | town      | country
    ----+-----------+--------------+----------------------+------+-----------+---------
      1 | Adriaan   | Brouwers     | De Coninckstraat 23  | 8750 | Zwevezele | Belgium
      8 | Nancy     | Lemmens      | Copernicuslaan 198   | 9000 | Gent      | Belgium
     12 | Adri      | Van Geluwe   | Jan Breydelstraat 21 | 8511 | Aalbeke   | Belgium
      3 | Nelly     | Verdonck     | Azalealaan 33        | 9080 | Lochristi | Belgium

    Summary: COUNT(id) = 4

Summary: COUNT(group) = 5

9. Hints for using PostgreSQL

This is no replacement for the PostgreSQL documentation. It only gives some hints for using PostgreSQL in combination with Postgres Forms.

9.1. Character encoding

When the Pgtcl package is loaded and initialised, it sets the environment variable PGCLIENTENCODING to UNICODE.

The pgintcl package does not do this.

In order to get the same behaviour of pfm with both Pgtcl and pgintcl, pfm always sets PGCLIENTENCODING to UNICODE.

Notes:

Because pfm uses clientencoding UNICODE, it can work with most database server encodings. PostgreSQL automatically performs the necessary conversions on the communications between client and server (see PostgreSQL documentation Section "Automatic Character Set Conversion Between Server and Client" which is a subsection of Chapter "Localization").

The default character encoding used by the PostgreSQL database server is SQL-ASCII. This character encoding is not suitable for texts containing special characters like ë, é, è, etc.

The encoding LATIN1 (also referred to as iso8859-1) already contains some special characters, but it lacks some characters like the Euro symbol (€) and the French character œ.

The encoding LATIN9 (also referred to as iso8859-15) is almost identical to LATIN1, but it includes the € and œ. It still cannot encode characters from Cyrillic, Chinese or Japanese scripts.

The encoding UNICODE (which is in fact UTF-8 in the context of PostgreSQL) includes all these and much more. It is meant to be able to encode all possible characters of all known written languages.

When you create a database, you can specify an encoding for the database (e.g. CREATE DATABASE example ENCODING='LATIN1' or CREATE DATABASE example ENCODING='UNICODE', see PostgreSQL documentation for more details).

9.2. Some general hints about SQL

Here are some hints about SQL. For full documentation see PostgreSQL documentation.

10. The example databases

You can install the example databases as explained in section 2.5. Install example database.

10.1. The addressbook database

The tables of the addressbook database have been created with the following SQL statements:

CREATE TABLE person (
    id serial primary key,
    christian_name text,
    name text,
    street text,
    town text,
    "ZIPcode" text,
    country text,
    category text,
    description text
);

CREATE TABLE "ZIPcodes" (
    town text,
    "ZIPcode" text
);

CREATE TABLE "group" (
    name text primary key,
    description text
);

CREATE TABLE memberlist (
    person integer,
    "group" text
);

The following figure shows the links between the tables of the sample database:

Example database

This database shows a typical example of a many-to-many relationship: a person can belong to more than one group, a group can have more than 1 person as member. Such a relationship is implemented as a combination of 2 one-to-many relationships:

  1. the one-to-many relationship from "person" to "memberlist"; and
  2. the one-to-many relationship from "group" to "memberlist".

Both relationships are implemented as "links" in pfm:

The referential integrity of the these relationships is guaranteed by PostgreSQL table constraints, which have been created with the following SQL statements:

ALTER TABLE ONLY memberlist
    ADD CONSTRAINT ref_person
    FOREIGN KEY (person) REFERENCES person(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY memberlist
    ADD CONSTRAINT ref_group
    FOREIGN KEY ("group") REFERENCES "group"(name)
    ON UPDATE CASCADE ON DELETE CASCADE;

A person can belong to more than 1 group, and a group can have more than 1 member, but a person cannot be member of the same group twice.

The following constraint guarantees that a person can only be member of a group once:

ALTER TABLE ONLY memberlist
    ADD CONSTRAINT memberlist_pkey
    PRIMARY KEY (person, "group");

10.2. The customer database

The second example data base which is included in the distribution is somewhat more complicated. It is a database with customers, invoices, products and orders.

The following image gives an overview of the structure of the customerdb.

Structure of customer data base