    Postgres Forms Help File

    Contents

    Copyright

    Introduction

    1. Main window
        1.1. Database menu
            1.1.1. Open data base
            1.1.2. Install/convert pfm_* tables
            1.1.3. Close data base
            1.1.4. Exit
        1.2. Reports/Queries
            1.2.1. Run SQL
            1.2.2. Run report
            1.2.3. Print result
        1.3. Tools
            1.3.1. Options
        1.4. Help
        1.5. Context menu

    2. Query mode of a form

    3. Edit mode of a form
        3.1. Update record
        3.2. Add record
        3.3. Delete record
        3.4. Status field
        3.5. Links to other tables
        3.6. Filling out a form
        3.7. Views

    4. The pfm_* tables
        4.1. pfm_form
        4.2. pfm_attribute
        4.3. pfm_value_list
        4.4. pfm_value
        4.5. pfm_link
        4.6. pfm_report
        4.7. pfm_section
        4.8. The links between the pfm_* tables

    5. Designing a form
        5.1. Modifying or adding a form
        5.2. Defining a form's attributes
        5.3. Defining links between forms
        5.4. Defining value lists

    6. Designing a report
        6.1. Creating a view
        6.2. Designing the report layout

    7. Hints for installing and using PostgreSQL
        7.1. Getting started
        7.2. Character encoding
        7.3. Creating a new database
        7.4. Some hints about SQL

    8. The sample databases
        8.1. Installing the addressbook database
        8.2. Structure of the addressbook database
        8.3. Installing the customer database

    Copyright

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

    Copyright (C) 2004 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'.

    Please send bug reports and feature requests via the corresponding
    facilities on the home page of Postgres Forms (pfm):

    http://gborg.postgresql.org/project/pfm/

    Please send all other comments or questions to the mailing list:

    pfm-comments@gborg.postgresql.org

    Introduction

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

        - to design forms for adding, modifying or deleting records of
          data base tables;

        - to design links (one to many relationships) from one form to
          another, for navigating in the data base;

        - to design and to generate reports based on the data in a table
          or view;

        - to edit and to execute SQL statements.

    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.4 of Tcl/Tk
    and with version 7.4.2. of the PostgreSQL database server. It has also
    been tested with PostgreSQL 8.0. For more information on Tcl/Tk and
    PostgreSQL look at http://www.tcl.tk and http://www.postgresql.org
    respectively.

    The latest version of pfm includes the 'tclkit' for Linux on PC. This
    means that it can also run on Linux PCs that do not have Tcl/Tk
    installed.

    The latest version of pfm is also available for Windows XP and it
    includes the 'tclkit.exe' which means that it does not require a
    separate installation of Tcl/Tk. It may work on other versions of
    Windows, but it has only been tested on Windows XP.

    Postgres Forms needs one of the Tcl interface packages pgtclng or
    pgintcl to communicate with PostgreSQL database server. Both can be
    obtained from http://gborg.postgresql.org.

    Two versions of pgintcl (1.5.0 and 3.0.0) are included in the latest
    version of pfm.

    1. Main window

    The main window has a menu-bar with menu-items:

    Database, Reports/Queries, Tools, Help.

    1.1. Database menu

    The Database item opens a sub-menu with the following items:

        - Open... : open a data base;

        - Close : closes the current data base;

        - Exit : exit from Postgres Forms.

    1.1.1. Opening a data base

    After selecting 'Open...', you are prompted to enter the parameters
    for opening a particular data base residing on a particular
    host. These parameters are (copy of the PostgreSQL documentation):

        - host: Name of host to connect to. If a non-zero-length string is
                specified, TCP/IP communication is used. Using this
                parameter causes a hostname look-up.

          Note: Since version 1.1.0 of pfm, the "hostaddr" connection
                parameter is no longer supported, because "psql" and the
                password file file do not support it either, which made
                pfm's behaviour unnecessary complicated. You can still
                enter an IP address in the "host" connection parameter
                (like for psql).

        - port : Port number to connect to at the server host, or socket
                filename extension for Unix-domain connections.

        - dbname : The database name.

        - user : User name to connect as.

        - password : Password to be used if the server demands password
                authentication.

          Notes: :

               1. When a database is opened in pfm, it is connected with
                  the PostgreSQL database server twice:

                      - 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 'yes'. 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.

               2. If the "usePGPASSWORD" option is 'no', 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

               3. If the "usePGPASSWORD" option is 'no', 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.

               4. 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 1.2.1. Options).

    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.

    The following figure shows the main window of Postgres Forms:

    Above the listbox, there are 2 radio buttons:

        - a radio button [ o Normal mode] to activate normal mode;

        - a radio button [ o Design mode] to activate design mode.

    In "Normal mode" only the user defined forms are visible.

    In "Design mode" only the forms for the pfm_* tables are
    visible. These are the forms which can be used to define forms, links,
    valuelists and reports:

    The following forms are predefined and can be used to add, modify or
    delete form and report definitions:

        - pfm_form : a table defining the forms of the data base;

        - pfm_attribute: a table defining the form attributes;

        - pfm_value_list : a table defining the value lists for
          attributes;

        - pfm_value : a table defining the values of the lists
          defined in pfm_value_lists.

        - pfm_link : a table defining the links between forms;

        - pfm_report: a table defining a report;

        - pfm_section: a table defining a report's sections, layout and
          fields.

    See section "4 The pfm_* tables" for details.

    1.1.2. Install/convert pfm_* tables

    When you open a database for the first time in pfm, you will be asked
    whether you want to install the pfm_* tables.

    If you click on "yes", the tables pfm_form, pfm_attribute, pfm_link,
    pfm_value_list, pfm_value, pfm_report, pfm_section and pfm_version are
    installed and initialised in the database that you are opening. This
    will enable you to define and use forms and reports in that database
    and to execute SQL statements using pfm's RunSQL feature.

    If you click on "no", the database is opened but not modified in any
    way. pfm will only allow you to execute SQL statements using pfm's
    RunSQL feature.

    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 click on "yes", the pfm_* tables are converted to the format
    compatible with the new version of pfm.

    Note:

        - Beware that 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 clik on "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.

    1.1.3. Close data base

    By selecting Database - Close, you close the currently open data
    base. The list-box on the main window becomes empty.

    1.1.4. Exit

    When you select Database - Exit, the Postgres Forms application is
    closed.

    1.2. Reports/Queries

    The "Reports/Queries" menu item has the following sub-items:

        - Run SQL: enter, edit and run SQL commands;

        - Run report : run a report that has been defined previously.

    1.2.1. Run SQL

    When you select this menu item, the following window is opened:

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

    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.

        - \? : returns the psql on-line help on the "\" command;

        - \h : returns the list of SQL statements for which psql provides
          on-line help;

        - \h <SQL statement> : returns the on-line help for the SQL
          statement.

    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 [Back] end [Forward] buttons.

    The result area is not cleared when another run is done, i.e. the
    result of a new run is appended to the result already present in the
    result area. You can clear the result area explicitly by pressing the
    [Clear] button.

    You can also import SQL statements from a file. After pressing the
    [Import SQL] button, you can select a file to be imported. Then, you
    are asked whether you want 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:

        - SQL files that are imported in either way are automatically
          converted to character encoding UTF-8. This is necessary because
          pfm uses clientencoding UNICODE.
          See 7.2. Character encoding for more details.

        - The original SQL file is not touched by this conversion. If you
          choose to 'offer the file to psql', the result of the conversion
          to UTF-8 is stored in a temporary file, which is then offered to
          psql.

        - SQL files that are offered to psql by typing a '\i' command in
          the SQL window are not converted to UTF-8. So, if your system's
          standard character encoding is not UTF-8, always invoke the '\i'
          command via the [Import SQL] button.

    The text in the result area can be printed or saved to file, using the
    [Print result] or [Save result] buttons respectively.

    The 'Print result' feature is described in more detail in section
    1.2.3. Print result

    Notes:

        - The result area is shared between the "Run SQL" and "Run report"
          commands. So, it is possible to combine the results from Run SQL
          and Run report into one file or into one printout.

        - Using the radio buttons [SQL] and [Report], it is possible to
          toggle between "SQL" and "Report" mode. When doing so, the
          result area is not cleared.

    1.2.2. Run report

    When you select this menu item, the following window is opened:

    The upper part of the window, shows the list of reports that have been
    defined in the database.

    When you press the [Run] button, the highlighted report is executed
    and its results are appended to the text already present in the result
    area.

    The text in the result area can be printed or saved to file, using the
    [Print result] or [Save result] buttons respectively.

    The 'Print result' feature is described in more detail in section
    1.2.3. Print result

    Notes:

        - The result area is shared between the "Run SQL" and "Run report"
          commands. So, it is possible to combine the results from Run SQL
          and Run report into one file or into one printout.

        - Using the radio buttons [SQL] and [Report], it is possible to
          toggle between "SQL" and "Report" mode. When doing so, the
          result area is not cleared.

    How to define a report is explained in a section 6. Designing a
    report.

    1.2.3. Print result

    When you press [Print result], a window is displayed which shows the
    print command as it is defined by the option 'printcmd' (see section
    1.3.1. Options).

    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.

    '%s' in the command is a placeholder for the name of the temporary
    file in which pfm stores the text to be printed. If the command does
    not contain '%s', pfm does not use a temporary file and sends the text
    to the 'standard input' of the application being called (works only on
    UNIX with certain applications such as lpr, a2ps, etc.)

    The command may also contain parameters for which the user is prompted
    to provide a value when the command is called. The syntax is as
    follows:

    $(parameter_name=default_value)

    where '=default_value' is optional.

    Examples:

        - UNIX: a2ps --printer=display --$(portrait_or_landscape=portrait)
                --rows=$(nr-of-rows=1) --columns=$(nr-of-columns=1)
                --major=rows --chars-per-line=$(nr_of_chars_per_line=90)
                --center-title=$(title=Report)

                This command accepts text on 'standard input' (no '%s' in
                command).

                It uses 'a2ps', a versatile tool that converts plain
                (ascii) text to postscript and which can print several
                virtual pages on one physical page.

                It sends the output to 'display', i.e. a print preview is
                shown in 'ghostview' from which it is possible to really
                print the result.

                It has parameters and default values for:

                    o orientation for the physical page (portrait or
                      landscape), default: portrait;

                    o number of rows (i.e. number of virtual pages on one
                      physical page in the vertical direction), default:
                      1;

                    o number of columns (i.e. number of virtual pages on
                      one physical page in the horizontal direction),
                      default: 1;

                    o number of characters per line on the virtual page
                      (the font size is automatically adjusted), default:
                      90;

                    o title, default: Report.

                The next figure shows the window that is displayed if the
                default print command is used with Print result:

        - Windows:

          {C:/Program Files/Windows NT/Bureau-accessoires/wordpad.exe} %s

                Since the command contains '%s', pfm creates a temporary
                text file containing the text to be printed and then lets
                'Wordpad' load that temporary file. In Wordpad you can
                then choose the font, margin, page orientation etc.

                Remark the curly braces enclosing the full filename of
                wordpad.exe. They are necessary because of the spaces
                within the filename.

                Notes:

                   - This command is also the default for Windows
                     platforms, but the full filename may differ from
                     system to system. Use the 'Paste filename' button to
                     paste a new filename into the option.

                   - Use '/' instead of '\' as directory name separator.

    1.3. Tools

    The tools menu has a number of menu items, which are described
    below.

    1.3.1. Options

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

    ~/.pfmrc ;

    On Windows platforms that file is

    APPDATA\pfm\pfm.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:

    By clicking on a [? >>] button, you get both on-line help for
    the corresponding option and a larger input window for editing the
    option's value:

    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.

    1.4. Help

    The Help menu has 3 sub-items:

        - Help file : displays this very file;

        - License : displays the GNU General Public License;

        - About : displays some general information about postgres
                forms, such as: version, copyright and whether it is using
                Pgtcl or pgintcl to communicate with PostgreSQL.

    1.5. 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.

    2. Query mode of a form

    When a form is opened, by pressing the [Open] button on the main
    window, it is first in "query mode".

    In "query mode", the form displays :

       - a text area containing the SQL SELECT related to the form (see
         4.1. pfm_form for an explanation of the
         form's SQL SELECT statement);

       - a radio button labeled "WHERE";

       - a text field, nearby the "WHERE" radio button, in which the user
         can type a conditional expression for the "WHERE" clause of the
         SQL SELECT statement;

       - a radio button labeled "ORDER BY";

       - a text field, nearby the "ORDER BY" radio button, in which the
         user can type an "ORDER BY" clause of the SQL SELECT statement;

       - an [Execute] button to execute the specified SQL statement;

       - a [Quit] button to return to the main window;

       - a button for each table attribute, labeled with the attribute's
         name and which can be used to paste the attribute's name into the
         "WHERE" or "ORDER BY" clause at the cursor position.

       - a button for table attributes with 'typeofget' tgList or tgLink
         (see section 4.2), labeled with the text 'select and paste value'
         and which can be used to select a value from a list and paste it
         into the "WHERE" or "ORDER BY" clause at the cursor
         position. Although it is possible to paste a value into the
         "ORDER BY" clause, it normally does not make sense.

    The following figure shows the query mode of the form for table
    "memberlist" with attributes: "person" and "group". The form also
    displays "christian_name" and "name", which are derived from the table
    "person". These fields are read-only.

    By pressing the "WHERE" and "ORDER BY" radio buttons, the user can
    choose whether to edit the "WHERE" or the "ORDER BY" clause.

    Initially, the status of the radio buttons is such that the "WHERE"
    clause can be edited. By pressing the "ORDER BY" radio button, the
    user can switch to the "ORDER BY" clause. By pressing the "WHERE"
    radio button, the user can return to the "WHERE" clause, etc.

    After pressing the "Execute" button, the SQL statement is executed,
    the selected records are stored in an internal buffer and the form
    enters the "edit mode".

    3. Edit mode of a form

    In "Edit mode" the form shows:

        - the SQL SELECT statement related to the form (see
          4.1. pfm_form for an explanation of the
          form's SQL SELECT statement);

        - the text 'Record ' followed by n/m where n = index of current
          record in the internal buffer, m = index of last record in the
          internal buffer;

          Note: The records are numbered starting from 1. The maximum
                value for n is m+1. Record m+1 is a dummy, empty record
                which cannot be updated or deleted. Its status is always
                "After last".

        - a text field with the name of the form;

        - a status field indicating whether the current record is "Not
          modified", "Updated", "Added", "Deleted", "After last", "Not
          added", "Updating", or "Adding";

        - the data of the current record (initially record number 1);

        - the buttons on the left, labeled with the attribute names, can
          be used to search the internal buffer for records with a
          particular value for the corresponding attribute;

          Note: Only the internal buffer is searched. If the record you
                are looking for exists in the database but is not loaded
                in the internal buffer, it cannot be found in this way.

        - buttons labeled [>>] which can be used to view the
          attribute's data in a muli-line text window instead of a one
          line entry;

        - a button [Help] to display the online help for the form;

        - the buttons [Next] and [Prev] which enable the user to scroll
          through the selected records;

        - a button [Update] which enables the user to modify the record
          that is displayed (see below for details);

        - a button [Add] to add new records (see below for details);

        - a button [Delete] to delete the displayed record;

        - a button for each link to another table, defined in the data
          base table "pfm_link" (see 4.5. pfm_link for details);

        - a [Back] button which enables the user to return to the form
          that was displayed before a "link" button was pressed;

          Note: The [Back] button is displayed only if the form that is
                being displayed is the result of pressing a "link" button.

        - a [Quit] button to return to the main window;

        - an [OK] button, which is used to confirm updates or additions of
          records and which is not displayed until [Update] or [Add] is
          pressed;

        - a [Cancel] button, which is used to cancel updates or additions
          of records and which is not displayed until [Update] or [Add] is
          pressed;

        - a text field showing the last UPDATE, INSERT or DELETE SQL
          statement and its result.

    When the form enters the "Edit mode", all the records' status fields
    initially have the value "Not Modified".

    3.1. Update record

    After the user presses 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 the user presses [OK]:

        - A 'BEGIN WORK' is issued to start a transaction.

        - A SELECT ... FOR UPDATE is done on the current record.

        - It is checked that the record has not been modified in the mean
          time by another user: the values returned by the SELECT FOR
          UPDATE should match the values in the internal buffer.

        - The new values for the record are stored in the data base by
          sending an 'UPDATE' and a 'COMMIT WORK' to PostgreSQL. This ends
          the transaction and releases the new record values to the data
          base.

        - the record's status field becomes "Updated" and the modified
          record is reloaded from the database. The result is that the
          record's read-only data, such as calculated fields and
          attributes of other than the main table are refreshed, taking
          into account the modified data.

        - The new values are also stored in the internal buffer. This
          means that even if the modifications are such that the record
          would no longer be selected by the original query, it stays in
          the internal buffer until the form leaves the "Edit mode".

        - Similarly, the record keeps its position in the internal buffer,
          even if that is no longer the position it would get according to
          the initial "ORDER BY" clause.

        - the SQL UPDATE statement, the SQL COMMIT WORK and their results,
          are displayed in a separate "result frame" at the bottom of the
          form.

    If the user presses [Cancel], the modifications are discarded.

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

        - after the first user has opened the form and before the first
          user has pressed [Update];

        - after the first user has pressed [Update] and before the first
          user has pressed [OK].

    In the first case, this is noticed by Postgres Forms when the user
    presses the [Update] button. In the second case it is noticed when the
    user presses 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 the screen by one user, it
    may be modified by another user.  This can happen:

        - after the first user has opened the form and before the first
          user has pressed [Update];

        - after the first user has pressed [Update] and before the first
          user has pressed [OK].

    In the first case, the 'reload record', which is executed when the
    first user presses [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 the first user presses [OK]. Then, the
    update operation is cancelled, and the user is notified.

    3.2. Add record

    After the user has 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 the user presses [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 leaves the "Edit
    mode".

    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 the user presses [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]:

        - the SQL INSERT statement, and its result, are displayed in a
          separate "result frame" of the form;

        - the record's status field becomes "Added";

        - the record is reloaded so that the read-only data, such as
          calculated fields and attributes of other than the main table,
          are also displayed for the newly added record;

        - the 'last record number' in the internal buffer is incremented
          with 1, which is shown on the 'current record number'/'last
          record number' display.

        - if the insert fails, the values entered by the user stay on the
          screen, the status field becomes "Not added" and the current
          record number becomes 'last record nr + 1'.

    3.3. Delete record

    If the user presses 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".

    3.4. 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 leaves the
    "Edit mode" and it is displayed on the form. The possible values are:

        - Not modified : the record was not modified since the form was
          opened;

        - Updated : the record has been updated since the form was opened;

        - Added : the recorded was added since the form was opened;

        - Deleted : the record has been deleted since the form was opened;

        - After last : the current record pointer is 1 after the last
                       record in the internal buffer.

        - Not added : the last "Add" operation has failed and the
                       current record pointer is 1 after the last record
                       in the internal buffer.

        - Updating : The user has pressed the [Update] button. pfm
                       is waiting for the user's input.

        - Adding : The user has pressed the [Add] button. pfm is
                       waiting for the user's input.

    3.5. Links to other tables

    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 in edit mode, all the links originating from
    that form, are displayed as buttons in the "links" frame.

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

    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.

    Links are defined in the pfm_link table. See 4.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.

    3.6. Filling out a form

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

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

        - tgDirect: the user directly types a value for the attribute;

        - tgExpression: the user can type an expression which is first
          evaluated before it is stored as a new value for the attribute
          (e.g. 1.0/7.75);

        - tgList: the user selects a value by means of a list-box
          containing a predefined list of possible values;

        - tgLink: the user selects a value by means of a list-box
          containing a list which is the result from a query on another
          table.

        - tgReadOnly: the attribute cannot be modified by the user.

    Notes:

        - When selecting a value from a list, the user can specify a
          search string at the top of the list box. Every time the [Go]
          button is pressed the next list item matching the search string
          is selected. The search is not case sensitive and may contain
          '*' and '?', where '*' means any sequence of characters and '?'
          means any single character.

        - When "type of get" is tgLink or tgList, the user normally
          selects a value from the list, but by pressing the [>>]
          button instead of opening the listbox, a new window is opened
          in which the user can directly type a value.

    3.7. 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.

    4. The pfm_* tables

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

    4.1. pfm_form

    A form has a one-to-one relation with just 1 database table. Only the
    data of that table can be administered by means of the form. This
    table is henceforth referred to as "the form's main table".

    However, the form also has a one-to-one relationship with just one SQL
    SELECT statement, which generates the data that are displayed on the
    form.

    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:

        - name : the name of the form (usually equal to the name of
          the form's table);

        - tablename : the name of the form's main table;

        - pkey : the primary key of the form's main table, which may
          consist of more than one attribute. In that case pkey is a SPACE
          separated list of the attributes of the primary key;

          Note: If pkey is empty, the form is read-only, since pfm is
                unable to uniquely identify a record. You can use the
                'oid' as primary key, but according to the PostgreSQL
                documentation that is not recommended, unless you set a
                UNIQUE constraint on the 'oid'.

        - sqlselect : the attribute list of the form's SQL SELECT
          statement, not including the word 'SELECT';

        - sqlfrom : the FROM clause of the form's SQL SELECT
          statement, not including the word 'FROM';

        - groupby : an optional 'GROUP BY' clause, not
          including the words 'GROUP BY';

        - view : a boolean indicating whether or not the
          "tablename" is a view;

        - showform : a boolean indicating whether the form is shown
          in "normal mode" (showform = 'true') or in "design mode"
          (showform = 'false'). Typically, showform is set 'true' for user
          defined forms and 'false' for the predefined pfm_* forms.

        - help : a text which is displayed when the user presses
          the [Help] key on the form.

    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:

        - the sqlselect, sqlfrom and groupby
          attributes of pfm_form; and

        - the optional WHERE and ORDER BY clauses provided by the user
          when opening the form.

    Note: The WHERE clause provided by the user when opening the form, is
          automatically converted to 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:
        -

    4.2. pfm_attribute

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

    It has the following attributes:

        - form : the "name" of the form to which the attribute
          belongs;

        - attribute : the name of the attribute; this must be equal
          to the name of the corresponding attribute of the form's SQL
          SELECT statement;

        - typeofattrib : the type of attribute:

            o taQuoted: the value provided by the user is put
                 between single quotes when it is transferred to SQL
                 UPDATE or INSERT statements;

            o taNotQuoted: the value provided by the user is not
                 quoted when it is transferred to SQL UPDATE or INSERT
                 statements.

              Hint: In general, all attribute values must be quoted, exept
                    the values or expressions for numeric attributes.

        - typeofget: defines how the user provides a value for the
          attribute; possible values are:

              o tgDirect: the user types the value directly;

              o tgExpression: the user types an expression which is first
                evaluated before it is passed to SQL UPDATE or INSERT;

                Note: Even with tgDirect it is possible to enter an
                      expression as new value for an attribute, but then
                      the expression is evaluated by PostgreSQL whereas
                      with tgExpression, the expression is first evaluated
                      by Tcl before the SQL statement is sent to
                      PostgreSQL.

              o tgList: the user selects a value by means of a list box
                containing a list of values defined in table "pfm_value";

              o tgLink: the user selects a value by means of a list box
                containing a list of values which is the result from a
                query on another table.

              o tgReadOnly: this attribute cannot be modified by
                the user.

                Note: All calculated attributes and all attributes from
                      tables other than the form's main table should be
                      declared 'read-only'. If this rule is not observed,
                      the Add and Update operations on this form will fail.

        - sqlselect: the SQL SELECT statement which is used to fill the
          list box with possible values for the attribute (only meaningful
          if typeofget = tgLink).

          Note :

             o The sqlselect may return more than 1 attribute. If so, all
               the attributes are displayed in the list-box, but only the
               first one is used for updating the attribute.

        - valuelist : the "name" of the value list defined in table
          "pfm_value_list" (only meaningful if typeofget = tgList);

        - nr: a number which determines the order in which attributes are
          displayed on the form;

        - default: a default value for this attribute which is used when
          adding a record. If the first character is an '=' sign, the
          following characters should be an SQL SELECT statement which
          returns just one value.

          Example:

          default: =SELECT nextval('seq_person_id')

          In this example the default value is the next value of the
          sequenece 'seq_person_id'.

    4.3. pfm_value_list

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

    Its only attribute is

        - name : a name uniquely identifying the value list.

    4.4. pfm_value

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

    It has the following attributes:

        - valuelist : the name of the valuelist to which this value belongs

        - value : a character string;

        - description : a description of the value.

    4.5. pfm_link

    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:

        - linkname : the name of the link, which is displayed on
          a link button on the "fromform";

        - fromform : the name of the form from which the link
          originates;

        - toform : the name of the form to which the link leads;

        - sqlwhere : the "WHERE"-clause which is used to open the
          "toform" and in which the value of an attribute of the
          "fromform" may be represented by $(attrib-x), where
          'attrib-x' is the name of the attribute;

        - orderby : an 'order by' clause which determines the order of the
          records in the 'toform';

        - displayattrib : a space separated list of attributes
          of the 'fromform', the value of which is displayed on the 'toform' to
          remind the user from which record the link originated.

    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).

    4.6. pfm_report

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

    pfm_report has the following attributes:

        - name: the name of the report. This is the name that
          appears in the selection list of the "Run Report" function.

        - description: free text describing the purpose of the
          report in more detail.

        - sqlselect: an SQL SELECT statement that generates the
          data for the report.

    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.

    4.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:

        - by applying one of the aggregate funtions: COUNT, SUM, AVG,
          STDDEV, MIN, MAX;

        - on the fields of the sections j (j >= i), between the last
          printed lower numbered section k (k < i), till the next (not
          yet printed) lower numbered section k (k < i).

    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:

        - report: the name of the report to which the section belongs

        - level: a number 1, 2, 3, 4, ... . The first level must be
          '1'. The next levels must be numbered consecutively. In the most
          simple report, there is only a section with level 1.

        - layout: can be "row", "column" or "table".

        - fieldlist: a space separated list of field specifiers,
          one for each field to be printed in the sections of this level
          (see below for details).

        - summary: a space separated list of summary field
          specifiers (see below for details).

    The fieldlist must be formatted as follows:

    {field_1 label_1 alignment_1} {field_2 label_2 alignment_2}...
                                         {field_N label_N alignment_N}

    where :

        - field_i is the name of one of the columns returned by the
          report's SQL SELECT statement;

        - label_i is a string which has to be used as label for printing
          the i-th field of this section; if it consists of more than 1
          word, it must be delimited by double quotes (" .... ");

        - alignment_i is optional; if present, it is either l or r,
          indicating whether this field should be left or right aligned.

          Notes :

              o The alignment is optional. If it is left out, left
                alignment is assumed by default.

              o The alignment only influences the table layout. Column and
                row layouts are unaffected by the alignment indicator.

              o Multi-line fields, i.e. fields containing more than one
                line of text are only formatted properly in a column layout.

    For every section, the layout can be defined as:

        - row: the section's field labels and field values are
          printed in one row in a format: label_1 : value_1; label_2 :
          value_2; ... etc.

        - column: the section's field labels are printed in a first
          column, the section's field values are printed in a second column.

        - table: the section's values are printed in a table with a
          column per field and a row per record, the section's field
          labels are used as column headers for the table.

    The summary must be formatted as follows:

    {field_1 aggregate_1 format_1} {field_2 aggregate_2 format_2}...
                                          {field_N aggregate_N format_N}

    where:

        - field_i is the name of a field defined in the fieldlist of
          either this section, or another, higher numbered section;

        - aggregate_i is one of the aggregate functions: COUNT, SUM, AVG,
          STDDEV, MIN, MAX (see below for details); and

        - format_i is an optional 'ANSI C sprintf' formatting string (see
          below for details). If it is left out, the number is printed
          with maximum precision.

    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:

        - COUNT: Counts the number of rows. In this case, the field_i that
               is specified only determines which section is counted.

        - SUM: Calculates the sum of all the values of the specified
               field.

        - AVG: Calculates the average of the values of the specified
               field.

        - STDDEV: Calculates the standard deviation for the values of the
               specified field:

               SQRT (SUM( (value_i - AVG(value))**2 ) / N)

    	   where :

                   - value_1, value_2, ... value_N are the values of the
                     considered field;

                   - AVG(value) is the average of the considered values;

                   - N is the number of values.

        - MIN: Calculates the minimum of the values of the specified
               field.

        - MAX: Calculated the maximum of the values of the specified
               field.

    '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:

        - 'MinWidth' is an integer defining the minimum width (as number
          of characters) for the number to be printed. If the number does
          not need so much space, spaces are inserted in front of the
          number, unless MinWidth is negative. In that case, spaces are
          appended at the end. If the number needs more space than
          MinWidth, more space is used.

        - 'Precision' is an integer defining how many digits to print
          after the decimal point, or, in the case of g or G conversion,
          the total number of digits to appear, including those on both
          sides of the decimal point

        - 'Conversion' is one of:

              o d : convert integer to signed decimal string. In this case,
                    there is no need to define a 'Precision'.

                    Example: %1d

                             prints an integer and uses as many characters
                             as required.

              o f : convert floating point number to fixed point
                    notation. In this case, 'Precision' defines the number
                    of digits to print after the decimal point. If there
                    are not enough digits available, trailing zeroes are
                    appended.

                    Example: %1.2f

                             prints a floating point number wiht 2 digits
                             after the decimal point and uses as many
                             characters as required.

              o e or E : Convert floating-point number to scientific
                    notation in the form x.yyye±zz, where the number of
                    y's is determined by the 'Precision' (default: 6). If
                    the precision is 0 then no decimal point is output. If
                    the E form is used then E is printed instead of e.

                    Example: %1.5E

                             prints a floating point number in the form
                             x.yyyyy E±zz

              o g or G : If the exponent is less than -4 or greater than
                    or equal to the precision, then convert floating-point
                    number as for %e or %E. Otherwise convert as for
                    %f. Trailing zeroes and a trailing decimal point are
                    omitted. In this case the 'Precision' specifies the
                    total number of digits to appear, including those on
                    both sides of the decimal point

                    Example: %1.4G

                              prints 2345.0 as 2345
                              prints 234567.0 as 2.346E+05
                              prints 0.003456 as 0.003456
                              prints 0.00003456 as 3.456E-05

    4.8. The links between the pfm_* tables

    The following drawing shows the links between the 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;

    5. 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 4. 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 1.2.1. Run SQL) or
          from 'psql', the interactive SQL terminal of PostgreSQL.

    5.1. Modifying or adding a form

    To modify or add a form:

        - press the [o Design mode] radio button on the main window. The
          pfm_form form now becomes visible in the listbox on the main
          window;

        - Select the pfm_form form and then open it;

        - If you want to modify an existing form, select the form you want
          to modify either by specifying a"WHERE" clause, or by scrolling
          through the already defined forms until you reach the one you
          want to modify;

        - If you want to add a new form, don't specify a "WHERE" clause,
          just press [Execute] and then press the [Add] button;

        - Now it is possible to enter/modify the form's definition, as
          described in section 4.1. pfm_form

    5.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 4.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 5.4. Defining value lists.

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

    5.3. Defining links between forms

    First proceed as explained in the section "5.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 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 the user of Postgres Forms to jump from one table/form to
    another while taking into account the relationships between the
    tables.

    5.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 query to select that value list and press the
    [Execute] 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, specify a query with no "WHERE"-clause Then press [Add] and
    specify the name of the new value list.

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

    6. Designing a report

    6.1. 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
    8. 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.

    6.2. Designing the report layout

    Enter the design mode by pressing the [o Design mode] radio button on
    the main window.

    Then select the form pfm_report and open it.

    Don't specify a query, press [Execute] and then [Add].

    Then fill out the form as follows:

        - name: Groups and persons

        - description : Lists all groups and their members

        - 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
                     ORDER BY g.name, p.name, p.christian_name

    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 4.7. pfm_section for more details on section definitions.

    When this report is run (Menu Reports/Queries, Run report, 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

    7. Hints for installing and using PostgreSQL

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

    7.1. Getting started (Only for UNIX systems)

    After you have installed PostgreSQL, you have to initialise the so
    called 'database cluster'. This is in fact the directory that will
    contain all the databases.

    To do so, you need to have the PostgreSQL user account. This is the
    UNIX user who owns all the data and who owns the server process. It is
    often the user with login id 'postgres', but that depends on how
    PostgreSQL is installed. This user is a kind of super-user who can
    access all databases, but it must not be the operating system's
    superuser.

        - Login as user postgres.

        - init the database cluster, e.g.

          initdb -D /var/lib/pgsql/data --encoding=LATIN1 --lc-collate=C

          See section 7.2. for the 'encoding issue'.

        - start the postmaster (i.e. the database server)

        - createdb postgres

        - createuser <user name> where <user name> is the
          login id of a user who will use PostgreSQL. Answer the questions
          from createuser. The rest of this section assumes that the user
          has the right to create/drop databases.

        - createdb <user name> where <user name> is the login
          id of a previously created user. This will allow the user to
          start psql by just typing 'psql', because, by default, the
          database with the user's name is openend.

    7.2. 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:

       - What PostgreSQL calls 'UNICODE' actually means 'UTF-8'.

       - pgintcl 1.5.0 only supports character encoding LATIN1.

    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 Chapter 20.3.1. Automatic Character Set Conversion Between Server
    and Client of the PostgreSQL 7.4.2 documentation).

    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.

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

    It is also worth considering to set your preferred encoding when
    running initdb. If you run e.g.

    initdb -D /var/lib/pgsql/data --encoding=LATIN1 --lc-collate=C

    then all database that are subsequently created, are by default
    LATIN1.

    7.3. Installing a new database

    First create the new database:

        - either createdb --encoding=LATIN1 newdbname;

        - or CREATE DATABSE newdbname ENCODING='LATIN1' from psql or from
          the Run SQL window of Postgres Forms.

    Then open newdbname and install the pfm_* tables by answering 'Yes'
    when asked whether you want to install the pfm_* tables.

    7.4. Some genreal hints about SQL

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

        - The SQL language is not case sensitive, but it is customary to
          use upper case for SQL statements.

        - PostgreSQL always converts the names of tables and attributes to
          lower case, regardless of the case typed by the user, unless
          these names are enclosed in double quotes. Example:

                CREATE TABLE Customer (
                     Id        serial,
                     Name      text);

          is implicitly and silently converted to:

                CREATE TABLE customer (
                     id        serial,
                     name      text);

          If you really want Customer, Id and Name, you have to write:

                CREATE TABLE "Customer" (
                     "Id"        serial,
                     "Name"      text);

        - SQL keywords are not allowed as names of tables or attributes,
          unless they are enclosed in double quotes. Example:

                CREATE TABLE order ( ...

          is not accepted because 'order' is an SQL keyword, but you can
          use

                CREATE TABLE "order"

          to create a table with the name 'order'.

        - Postgres Forms automatically encloses the names of tables and
          attributes in double quotes for the SQL statements it generates
          itself. However, the user still has to take care when entering
          SQL statements, or parts of SQL statements, in the pfm_* tables.

    8. The sample databases

    8.1. Installing the addressbook database

    The addressbook sample data base is included in the distribution and
    can be installed as follows:

       1. Create the database:

          Open a database that you can access in pfm.

          Start the "Run SQL" feature with "Reports/Queries -> Run SQL".

          Type and run the following SQL statement:

          CREATE DATABASE addressbook ENCODING = 'LATIN1';

          This assumes that you are known to PostgreSQL as a user who has
          the right to create databases.

          Close the database from which you issued this command.

       2. Fill the database:

          Open the addressbook database in pfm.

          When you are asked whether you want to install the pfm_* tables,
          answer 'No'.

          Menu 'Reports/Queries -> Run SQL'.

          Press the [Import SQL] button.

          Select the 'install_addressbook.sql' file in the directory in
          which you have installed pfm.

          Choose 'Offer file to psql' and press the [Run] button.

       3. Now you are ready to experiment with the addressbook database in
          pfm.

    Note: Do not run the install_addressbook.sql script on a database that
          already contains the pfm_* tables.

    8.2. Structure of the addressbook database

    The tables of the sample 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:

    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:

        - on the form "person" there is a link button labelled "Groups"
          which opens the "memberlist" form in which all the memberlist
          records for the currently displayed "person" are loaded
          (one-to-many relationship "person -> memberlist");

        - on the form "memberlist" there is a link button, labelled
          "Person", which opens the "person" form in which the person,
          referenced by the currently displayed "memberlist" record, is
          loaded (many-to-one relationship "memberlist -> person");

        - on the form "group" there is a link button labelled "Members"
          which opens the "memberlist" form in which all memberlist
          records for the currently displayed "group" are loaded
          (one-to-many relationship "group -> memberlist");

        - on the form "memberlist" there is a link button, labelled
          "Group", which opens the "group" form in which the group,
          referenced by the currently displayed "memberlist" record, is
          loaded (many-to-one relationship "memberlist -> group");

    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");

    8.3. Installing the customer database

    The second sample data base which is included in the distribution is
    somewhat more complicated. It is a database with customers, invoices,
    products and orders. You can install it as follows:

       1. Create the database:

          Open a database that you can access in pfm.

          Start the "Run SQL" feature with "Reports/Queries -> Run SQL".

          Type and run the following SQL statement:

          CREATE DATABASE customerdb ENCODING = 'LATIN1';

          This assumes that you are known to PostgreSQL as a user who has
          the right to create databases.

          Close the database from which you issued this command.

       2. Fill the database:

          Open the addressbook database in pfm.

          When you are asked whether you want to install the pfm_* tables,
          answer 'No'.

          Menu 'Reports/Queries -> Run SQL'.

          Press the [Import SQL] button.

          Select the 'install_customerdb.sql' file in the directory in
          which you have installed pfm.

          Choose 'Offer file to psql' and press the [Run] button.

       3. Now you are ready to experiment with the customer database in
          pfm.

    Note: Do not run the install_customerdb.sql script on a database that
          already contains the pfm_* tables.

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