# The KPoGre Handbook

Lumir
Vanek

    lvanek@users.sourceforge.net

2004-2009
Lumir Vanek
&FDLNotice;
23/04/2009
1.8.2

&kpogre; is a PostgreSQL administration tool for KDE.

KDE
PostgreSQL

## Introduction

&kapp; is a PostgreSQL administration tool. It allow browse through system catalog, create, modify and drop database objects, run administrative tasks, execute SQL statements, edit database tables, executes functions. &kpogre; looks like this:

Left side contain dockable tree with connected PostgreSQL servers and their objects structure. Also, there is toolbox with code snippets - most commonly used functions.

Right side contain MDI child's windows. There is Properties for selected item. Also other windows, may be opened here: Query/result windows, Datatable windows, Executor windows, Debugger windows.

Here's a screenshot of &kpogre;

&kapp; have various toolbars - basic one with standart commands and database related commands. Third toolbar contain Konqueror-like path bar, that display path of selected tree item.

Also next toolbars can be displayed with specific actions for Query/result windows, Datatable windows and others.

The Pathbar shows the path to the selected item, You can type a path here and press Enter to go to it. You may use combobox in path bar for select item. The black icon at the left hand end of the Pathbar clears the text entry box.

## Using &kpogre;

### Objects icons

#### Main objects

Connection to PostgreSQL server

System tablespace

User tablespace

Unconnected database

Template database

User database

Database super-user

Database user - can create databases

Database user

Groups of users

System schema

User schema

Type cast

Procedural language

#### Schema objects

Aggregate

Conversion

Domain

Function in C language

Function in PL/pgSQL

Function in SQL

Other function

Function argument

Operator class

Operator

Ordinary Table

TOAST Table

Base datatype

Composite datatype based on table row structure

Standalone composite datatype

Other datatype

Sequence

View

#### Table objects

Check constraint

Index

Primary key

Foreign key

Rule

Trigger

#### Column types

OID - object identifier

TID - tuple (row) identifier

CID - command identifier

XID - transaction identifier.

Character

XML

Numeric

UUID

Binary

Bit String

Boolean

Time

Date

Geometric

Network address

Other column

### Connecting to server

Choose Database -> Connect menu item and fill connect dialog. Press OK to connect.

image 1

You may disconnect from server selecting it and using Database -> Disconnect menu item.

### Using bookmarks

On database toolbar, there is bookmarks button that can be used to bookmarking connection to PostgreSQL server. Select server in left tree view. Click to Bookmarks button on left toolbar and choose Add bookmark.

image 1

Now you may connect to server using Bookmarks button and choosing item in menu, instead of using Database -> Connect command and fill all items to connect dialog.

image 2

### OID links

Most of objects in system catalog have OID as identifier that is unique through database. In property pages, &kpogre; displays objects referenced with OID using different color.

In folowing screenshot there is list of functions. First column in right side contain their names as OID links. User may move mouse cursor to OID link, pointer is changed to hand and OID value is shown as tooltip.

image 1

User may double click on OID link and &kpogre; jump to referenced object. Using this feature is easy to browse system catalog.

Now we see choosed function. On new object, there is another referenced object - function datatype. Double clicking on it user can jump to this object. Or we may return back to previous object using View -> To back command.

image 2

Jumping through object tree is logged and user may return back using View -> To Back command and go forward using View -> To Forward or with related buttons on toolbar.

Selecting items in left tree view is not logged to back/forward navigation. Another way to return to previously visited item is selecting it in Path bar combo.

### Monitoring Database Activity

If you select server, database, table, index or sequence , there are statistics informations on property page. These informations are obtained from The Statistics Collector.

The PostgreSQL server must be configured to collect statistics informations:

image 1

Statistics pages contains Refresh button that allow reload statistics from database.

Statistics information page

### Querying database

Use File -> New to open new SQL query/result window. It can be used to enter SQL statement, send it to server and see result.

Tab icons in tabs may have three colors:

- Black - Query / result window is ready to enter SQL

- Red - SQL was sent to server and user waits for result.

- Blue - Result is displayed and query is not changed after run last query.

Query / result window

#### SQL query

SQL query can be saved and opened using standard menu entries in File menu. For editing SQL is used Kate part component - editing is similar than in Kate editor.

While editing, code completion box can be showed pressing Ctrl+Space. Code completion offers SQL keywords, schemas, tables, views and functions. Code completion is case sensitive, to compete SQL keywords type them in upper case leters.

Code completion

When you type table name, or function returning set name - and then press dot, code completion box containing columns is displayed.

Note, that code completion depends from informations in tree view. When you create, modify or drop database objects using Wizards or scripts, refresh database to have completion informations up to date.

#### Running query

Enter statement in SQL language. You may use PostgreSQL language extensions, of course.

If user have connected more than one server or database, then it is necessary choose proper server and database for running query in comboboxes.

Choose Query -> Run query menu item or press toolbar button with rocket. Query will be sent to server, terminal icon change color to red. It is possible to continue work with rest of program.

It is possible to terminate running query using Query -> Stop query menu item or pressing toolbar button.

#### Options

Pressing Options button user can show controls for following options:

##### XAct - transaction type

User can choose type of transaction for executing query:

- Transaction - Standard back-end transaction.

- Robust trans - Slightly slower, better-fortified version of transaction.

- Nontransaction - Query will not run inside transaction.

##### Explaining and analyzing SQL

There are checkboxes to prepend keywords EXPLAIN [ANALYZE] [VERBOSE] before SQL statement. See PostgreSQL documentation for more informations about explaining and analyzing SQL.

##### Execution time

Also you may see information about last query execution time. Note, that this time is meassured on client side, and it differ from time meassured by EXPLAIN ANALYZE.

#### Looking at result

When query result is returned from server, it is displayed in result area. It may be text or table, depending from query.

When table result is larger than 500 rows, only 500 rows fetched initially to table. User may fetch another bunch of rows using Query -> Fetch next and Query -> Fetch all.

If you make mouse right-click over result table, menu containing some search and clipboard actions is displayed.

Clipboard actions

Content of result table may be saved to CSV file using File -> Export result.

### Edit tables

&kpogre; contain Datatable - editor for editing database table content. Select table you wish to edit in tree, make right mouse click to display menu and choose Edit table content. Table must have primary key.

Opening datatable

KPoGre shows dialog, you may enter WHERE predicate to limit anmount of edited rows and ORDER BY predicate to specify sort orded of edited data. It is good practice specify at least one sort column, because updated rows are returned on end of result when you reload data and sort order is not specified.

Datatable window

&kpogre; shows Datatable with table content. Also toolbar is displayed.

#### Datatable menu and toolbar actions

When Datatable is opened, you may use items in Datatable submenu from main menu bar. &kpogre; also shows toolbar with actions for it. Aditionally, you may reach those actions by make right click over datatable to show menu. On this menu, there are some useful clibpoard actions.

&kpogre; with datatable

##### Moving current row

There are actions for navigate to First row, Previous row, Next row and Last row. This same effect have navigate keys on keyboard - Up, Down, Home and End keys. Current row can be also changed using mouse by selecting it.

##### Edit data

When cell is selected, you may start edit it's content using Edit cell action, or by pressing F2, or simply by start typing on keyboard. Current cell content can be cleared by Clear cell or pressing Delete. Cell editing can be canceled by pressing Esc.

Insert new row using action Insert row or pressing Ctrl+Insert. New row is inserted at end of table and it's ready to fill data. User must fill all fields, except NULLable or with DEFAULT value. When table have primary key autogenerated from sequence, leave it's cell blank, Datatable select next sequence value and use it. After fill row, use Update changes or presses Ctrl+Enter to perform INSERT operation.

Delete current row using action Delete row or pressing Ctrl+Delete.

##### Commit or cancel changes

Datatable is row oriented editor. When a user makes changes to a cell in the table, current row's data are stored to backup buffer and cell is marked for update. The Datatable will not send changes in the current row to the database until the user moves to a different record in the datatable or use Update changes or presses Ctrl+Enter.

Row editing or inserting can be canceled using Cancel changes, or by pressing Esc. In this case, data are restored from backup buffer or insert is cancelled.

##### Reload data

Using action Reload data user can perform SQL query to actual data in database table. &kpogre; displays dialog to give chance to modify WHERE or ORDER BY clauses.

Using action Stop reload data user can interrupt this operation.

##### Clipboard actions

When user make right mouse click over Datatable, &kpogre; shows popup menu, where are some useful actions for copy current cell, row or entire table to clipboard.

Content of Datatable may be saved to CSV file using File -> Export result.

#### Large objects

If table contain large object column (text or bytea), its content is stripped to reasonable size for display it. Those fields cannot be edited by inline editor. Instead, press F2 or make double click on cell and &kpogre; display popup menu.

##### Editing Texts - CLOBs

Text fields can be loaded from disk file, saved to it ...

Text field menu

... or you can launch dialog for edit it.

Editing text

##### Editing Byte arrays - BLOBs

Byte array fields can be loaded from disk file or saved to it.

Text field menu

##### Encoding notes

When you save large object field to file, it's encoded with this same encoding, that have connection to PostgreSQL server, when you load from file, expected encoding is also the same as connection have. Select server in left tree to check wich encoding is used for connection:

Checking server connection

### Execute functions

&kpogre; contain Executor - for executing database functions. Select function you wish to run in tree, make right mouse click to display menu and choose Execute function.

Opening Executor

KPoGre open Executor window with function arguments and result area. Also toolbar is displayed.

Executor window

#### Function arguments

Enter arguments values to table. Convertion rules are the same as in Datatable.

#### Execute function

Choose Executor -> Execute menu item or press toolbar button.

It is possible to terminate running function using Executor -> Stop menu item or pressing toolbar button.

#### Options

##### XAct - transaction type

User can choose type of transaction for executing function:

- Transaction - Standard back-end transaction.

- Robust trans - Slightly slower, better-fortified version of transaction.

- Nontransaction - Query will not run inside transaction.

##### Explaining and analyzing SQL

There are checkboxes to prepend keywords EXPLAIN [ANALYZE] [VERBOSE] before SQL statement. See PostgreSQL documentation for more informations about explaining and analyzing SQL.

##### Execution time

Also you may see information about last query execution time. Note, that this time is meassured on client side, and it differ from time meassured by EXPLAIN ANALYZE.

#### Looking at result

When query result is returned from server, it is displayed in result area. It may be text or table, depending from function.

When table result is larger than 500 rows, only 500 rows fetched initially to table. User may fetch another bunch of rows using Executor -> Fetch next and Executor -> Fetch all.

If you make mouse right-click over result table, menu containing some search and clipboard actions is displayed.

Clipboard actions

Content of result table may be saved to CSV file using File -> Export Result.

### Debug PL/pgSQL functions

&kpogre; contain Debugger - for debugging PL/pgSQL database functions. Select function you wish to debug in tree, make right mouse click to display menu and choose Debug function. You need PostgreSQL version 8.3 or newer for this functionality.

Opening Debugger

KPoGre open debugger window. Also toolbar is displayed.

Debugger window

#### Function parameters

Enter arguments values to table. Convertion rules are the same as in Datatable.

Function arguments

#### Start debugging

Choose Debugger -> Start in debugger menu item or press toolbar button. If debugging starts successfully, KPoGre place execution mark to first line of code. Also, menu and toolbar actions for debugging are enabled. Look at Debugger tab, where are some informations from debugger process.

Start debugging

It is possible to terminate running function using Debugger -> Stop menu item or pressing toolbar button.

#### Ataching another functions

When debugger is started, its possible to attach another functions to it. It is necessary for Stepping into called function.

Ataching another functions

#### Breakpoints

When debugger is started, its possible to set or drop breakpoints. Place cursor to line, where you want to toggle breakpoint, and use Debugger -> Toggle Breakpoint

Breakpoints

If breakpoint is sucessfully set/dropped, mark is placed/removed to left ribbon.

#### Debugging

You may use obvious actions for function tracing:

- Step over - execute one line and stop.

- Step into - when another function is called, debugger step into it and pause at first line. It works only if you have called function attached to debugger session.

- Continue processing to next breakpoint or end.

##### Watching and changing Variables

While tracing, you may see variable values. Variables, that are changed from previous pause are highlighted by mark.

Watching and changing Variables

It is possible to change varible value, while debugging. Select row with variable and make mouse right click over variables table to show context menu.

##### Watching output

While tracing, you may see notices, sent from debugged function.

Watching output

##### Stack

On Stack tab, you may see informations about function calling hierarchy.

Stack

#### Looking at result

When function is finished, result (if any), it is displayed in result area.

When table result is larger than 500 rows, only 500 rows fetched initially to table. User may fetch another bunch of rows using Executor -> Fetch next and Executor -> Fetch all.

If you make mouse right-click over result table, menu containing some search and clipboard actions is displayed.

Looking at result

Content of result table may be saved to CSV file using File -> Export Result.

## &kpogre; Wizards

### Create object

There are two ways to create object using wizard. First - you may select server or database or schema or table in left tree view and choose menu command Database -> Wizard. &kpogre; displays dialog where you may choose what you want to create. Dialog contents is depends from selected object:

Object type dialog
Object type dialog
Object type dialog

A second way is using object menu - in left tree view. Select object and make right button click on it to display object menu. This menu contain item for creating child objects. For example if you want to create new table, select tables folder and from object menu choose Create table

### Rename object

Select object in tree view and make right button click on it to display object menu. If rename is posible, this menu contain item for Rename object. Choose this menu item and follow Wizard for rename object.

Rename object Wizard

For example if you want to rename namespace on first wizard page you may enter new namespace name:

Rename object Wizard

In next step you may rewiew SQL command to RENAME object. When you press Next button, coman will be send to server.

Rename object Wizard

In final second step you may see operation result. Press Finish button to close wizard dialog.

If object menu not contain Rename item, renaming may be possible using Modify object Wizard.

### Modify object

Select object in tree view and make right button click on it to display object menu. This menu contain item for Modify object.

Modify object Wizard

For example if you want to modify view, &kpogre; display this Wizard.

Modify object Wizard

In next step you may rewiew SQL command. When you press Next button, coman will be send to server.

Modify object Wizard

In final step you may see operation result. Press Finish button to close wizard dialog.

### Set object owner

This operation changes the owner of the selected object. It is applicable for tablespace, database, schema, table, index, domain and type.

Press Next button.

### Set object privileges

Privileges can be granted or revoked for table, view, sequence, database, tablespace, function, language or schema. Those objects have menu items for grant or revoke privileges and also have Privileges tab in property window.

There are two ways for setting privileges. First - using wizards from object menu - selecting object, make right mouse click and select Grant privileges or Revoke privileges menu items. A second one is using Privileges tab in property window. There is list of users or groups and their privileges displayed using check boxes. User may change it's state and then press Update button.

First way - using wizard is suitable for granting new privileges for users or groups that are not in privilegs list. Grant privileges wizard have the same look and feel as last step from create object wizard, where is also possibility to grant privileges.

While granting privileges - if Also give GRANT privileges is checked, the recipient of the privilege may in turn grant it to others. Without a grant option, the recipient cannot do that. Grant options can only be granted to individual users, not to groups or PUBLIC.

While revoking privileges - if Only revoke GRANT privilege is choosed, only the grant option for the privilege is revoked, not the privilege itself. Otherwise, both the privilege and the grant option are revoked.

If a user holds a privilege with grant option and has granted it to other users then the privileges held by those other users are called dependent privileges. If the privilege or the grant option held by the first user is being revoked and dependent privileges exist, those dependent privileges are also revoked if Cascade is checked, else the revoke action will fail. This recursive revocation only affects privileges that were granted through a chain of users that is traceable to the user that is the subject of this REVOKE command. Thus, the affected users may effectively keep the privilege if it was also granted through other users.

Second way is better for editing existing privileges. Simply check or uncheck privileges and press Update button. &kpogre; displays two-steps wizard that contain SQL commands to make this change.

### Drop object

Select object in tree view and make right button click on it to display object menu. This menu contain item for drop objects. For example if you want to drop table, select it and from object menu choose Drop table &kpogre; run two-steps Wizard.

Running drop object Wizard

In first step you may rewiew SQL command to DROP objet. When you press Next button, coman will be send to server.

Drop object Wizard

In final second step you may see operation result. Press Finish button to close wizard dialog.

Drop object Wizard

### Create or modify Group

#### Step 1 - Group name and members

In first step, enter group name. Optionally, you may enter System ID for new group. If you leave it blank, PostgreSQL assign this ID automatically. Also, define users that becomes members of this group.

Group name and members

#### Step 2 - Review SQL statement

In second step review SQL command that create group. Press Next button to perform operation.

Review SQL statement

### Create or modify User

#### Step 1 - User name, rights and password

In first step, enter user name. Optionally, you may enter user ID for new user. If you leave it blank, PostgreSQL assign this ID automatically. Also, define if user can create databases and other users.

Enter twice password for user, choose password encryption mode and if you want - set accout expiration date.

User name, rights and password

#### Step 2 - Group membersip

In second step define users that becomes members of this group.

Group membersip

#### Step 3 - Review SQL statement

In third step review SQL command that create user. Press Next button to perform operation.

Review SQL statement

### Create Tablespace

#### Step 1 - Tablespace name and another properties

A tablespace allows define an alternative location path on the file system where the data files containing database objects (such as tables and indexes) may reside. This allow to split data to more physical disc and improve performance. Tablespaces support is introduced to PostgreSQL 8.

In first step, enter tablespace name. Enter location path for tablespace. Note, that using directory browse button has sense only if you manage server on local machine.

Optionally select Owner of tablespace, if it is different than actual user that in logged in.

Tablespace name and another properties

#### Step 2 - Grant create privileges

In second step define users with privileges to CREATE DATABASE, CREATE SCHEMA, CREATE TABLE, CREATE INDEX or ADD CONSTRAINT.

Grant create privileges

#### Step 3 - Review SQL statement

In third step review SQL command that create tablespace. Press Next button to perform operation.

Step 3 - Review SQL statement

### Create Database

#### Step 1 - Database name and another properties

Enter name for new database and optionally choose another properties.

Normally, the creator becomes the owner of the new database. Select another user as Owner, if you want.

For PostgreSQL 7.x or older - an alternative Location can be specified in order to, for example, store the database on a different disk. The path must have been prepared with the initlocation command. Location is interpreted as an environment variable name, which must be known to the server process.

For PostgreSQL 8 or newer - A Tablespace can be specified. If not specified, the same tablespace that is default for the template database is used.

By default, the new database will be created by cloning the standard system database template1. A different Template can be specified. Listed are only unconnected databases.

The optional Encoding parameter allows selection of the database encoding. When not specified, it defaults to the encoding used by the selected template database.

Step 1 - Database name and another properties

#### Step 2 - Define user rights

This step allow grant specific privileges on database to one or more users or groups of users. These privileges are added to those already granted, if any.

Select users or groups (multiple select allowed) or check PUBLIC for grant to all users.

The possible privileges are:

- CREATE - Allows new schemas to be created within the database.

- TEMPORARY - Allows temporary tables to be created while using the database.

Check one or more privileges, or ALL.

Define user rights

Click on Add button. One or more grant commands is inserted to list. Grant privilege may be removed from list, using Delete button. After defining all privileges press Next button.

#### Step 3 - Review SQL statement

In second step review SQL command that create database. Press Next button to perform operation.

Review SQL statement

### Analyze database

#### Step 1 - Review SQL statement

Analyze examines every table in the current database and collects statistics about the contents of tables, and stores the results in the system table pg_statistic. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.

Then, press Next button.

#### Step 2 - Check operation result

Finally, check operation result.

### Reindex database

#### Step 1 - Review SQL statement

Reindex rebuilds an index based on the data stored in the table, replacing the old copy of the index. There are two main reasons to use:

- An index has become corrupted, and no longer contains valid data. Although in theory this should never happen, in practice indexes may become corrupted due to software bugs or hardware failures. REINDEX provides a recovery method.

- The index in question contains a lot of dead index pages that are not being reclaimed. This can occur with B-tree indexes in PostgreSQL under certain access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages.

This function recreate all system indexes of a specified database. Indexes on user tables are not processed. Also, indexes on shared system catalogs are skipped except in stand-alone mode.

#### Step 2 - Check operation result

Check operation result.

### Dump database

This wizard is a shell for pg_dump command. pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).

#### Step 1 - Dump database parameters

Enter Output file used for storing database backup.

Selects the Format of the output:

- plain-text SQL - Output a plain-text SQL script file.

- tar archive - Output a tar archive suitable for input into pg_restore. Using this archive format allows reordering and/or exclusion of database objects at the time the database is restored. It is also possible to limit which data is reloaded at restore time.

- custom archive - Output a custom archive suitable for input into pg_restore. This is the most flexible format in that it allows reordering of loading data as well as object definitions. This format is also compressed by default. For this format specify the Compression level.

Selects the Options:

- Dump only the data, not the schema - This option is only meaningful for the plain-text format. For the other formats, you may specify the option when you call pg_restore.

- Dump only the object definitions (schema) - If this option is not specified, all non-system schemas in the target database will be dumped.

- Include large objects - Include large objects in dump.

- Include command to CREATE the database - Begin the output with a command to create the database itself and reconnect to the created database.

- Generate 'DROP objects' statements - Output commands to clean (drop) database objects prior to (the commands for) creating them.

- Dump data as INSERT commands - Dump data as INSERT commands (rather than COPY). This will make restoration very slow, but it makes the archives more portable to other SQL database management systems.

- With explicit column names - Dump data as INSERT commands with explicit column names (INSERT INTO table (column, ...) VALUES ...). This will make restoration very slow, but it is necessary if you desire to rearrange the column ordering.

- Ignore version mismatch - Ignore version mismatch between pg_dump and the database server.

- Dump OIDs - Dump object identifiers (OIDs) for every table. Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used.

- No owner - Do not output commands to set ownership of objects to match the original database.

- Verbose mode - Output detailed object comments and start/stop times to the dump file, and progress messages to standard error.

- No privileges - Prevent dumping of access privileges (grant/revoke commands).

- Disable dollar quoting for function - This option disables the use of dollar quoting for function bodies, and forces them to be quoted using SQL standard string syntax.

- Disable triggers - This option is only relevant when creating a data-only dump. It instructs pg_dump to include commands to temporarily disable triggers on the target tables while the data is reloaded. Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload.

- Set session authorization - Output SQL standard SET SESSION AUTHORIZATION commands instead of OWNER TO commands. This makes the dump more standards compatible, but depending on the history of the objects in the dump, may not restore properly.

Dump database

#### Step 2 - Review command

In this step, you may review command, before running it.

Dump database

#### Step 3 - Console with operation

In this step, pg_dump command is run using Console. Wait for it and press Finish button.

Dump database

### Restore database

This wizard is a shell for pg_restore command that restore a PostgreSQL database from an archive file created by pg_dump.

#### Step 1 - Restore database parameters

Enter Input file of the archive file to be restored.

Selects the Options:

- Restore only the data, not the schema - Restore only the data, not the schema (data definitions).

- Restore only the schema, not the data. Sequence values will be reset - Restore only the schema (data definitions), not the data.

- Clean (drop) database objects before recreating them - Drop objects before creare them.

- Exit on error - Exit if an error is encountered while sending SQL commands to the database. The default is to continue and to display a count of errors at the end of the restoration.

- Ignore version mismatch - Ignore version mismatch between pg_dump and the database server.

- No owner - Do not output commands to set ownership of objects to match the original database.

- Verbose mode - Output detailed object comments and start/stop times to the dump file, and progress messages to standard error.

- No privileges - Prevent dumping of access privileges (grant/revoke commands).

- Disable triggers - This option is only relevant when creating a data-only restore. It instructs pg_dump to include commands to temporarily disable triggers on the target tables while the data is reloaded. Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload.

- Set session authorization - Output SQL standard SET SESSION AUTHORIZATION commands instead of OWNER TO commands. This makes the dump more standards compatible, but depending on the history of the objects in the dump, may not restore properly.

Dump database

#### Step 2 - Review command

In this step, you may review command, before running it.

Dump database

#### Step 3 - Console with operation

In this step, pg_restore command is run using Console. Wait for it and press Finish button.

Dump database

### Create Schema

#### Step 1 - Schema name and owner

A schema is essentially a namespace: it contains named objects (tables, data types, functions, and operators) whose names may duplicate those of other objects existing in other schemas. Named objects are accessed either by "qualifying" their names with the schema name as a prefix, or by setting a search path that includes the desired schema(s). Unqualified objects are created in the current schema (the one at the front of the search path, which can be determined with the function current_schema).

Enter name for new schema (AKA namespace) and optionally choose Owner - the user who will own the schema. If omitted, defaults to the user executing the command. Only superusers may create schemas owned by users other than themselves.

For PostgreSQL 8 or newer - a Tablespace can be specified. Choose the tablespace that is to be the default tablespace for all new objects created in the schema. If not supplied, the schema will inherit the default tablespace of the database.

Schema name and owner

#### Step 2 - Define user rights

This step allow grant specific privileges on schema to one or more users or groups of users. These privileges are added to those already granted, if any.

Select users or groups (multiple select allowed) or check PUBLIC for grant to all users.

The possible privileges are:

- CREATE - Allows new schemas to be created within the database.

- USAGE - Allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema.

Check one or more privileges, or ALL.

Define user rights

Click on Add button. One or more grant commands is inserted to list. Grant privilege may be removed from list, using Delete button. After defining all privileges press Next button.

#### Step 3 - Review SQL statement

In second step review SQL command that create schema. Press Next button to perform operation.

Step 3 - Review SQL statement

### Create Language

Using this wizard you can register a new procedural language with a PostgreSQL database. You need PostgreSQL superuser privilege to register a new language. Before registering language, you must have registered handler function.

#### Step 1 - Language name and another properties

Enter name for new language and choose a call handler that is responsible for executing functions written in the language. Optionally, choose validator.

Trusted specifies that the call handler for the language is safe, that is, it does not offer an unprivileged user any functionality to bypass access restrictions. If this key word is omitted when registering the language, only users with the PostgreSQL superuser privilege can use this language to create new functions.

Step 1 - Language name and another properties

#### Step 2 - Grant usage privileges

This step allow grant usage privileges on language to one or more users or groups of users.

Select users or groups (multiple select allowed) or check PUBLIC for grant to all users.

Step 2 - Grant usage privileges

Click on Add button. One or more grant commands is inserted to list. Grant privilege may be removed from list, using Delete button. After defining all privileges press Next button.

#### Step 3 - Review SQL statement

In second step review SQL command that create language. Press Next button to perform operation.

Step 3 - Review SQL statement

### Create Domain

#### Step 1 - Domain name

Domains are useful for abstracting common fields between tables into a single location for maintenance. For example, an email address column may be used in several tables, all with the same properties. Define a domain and use that rather than setting up each table's constraints individually.

If you start this wizard when database is selected, choose namespace (AKA schema) for new domain. When you run wizard from schema or views folder, actual schema is used.

Enter name for new domain.

Domain name and properties

#### Step 2 - Domain properties

Choose Data type of the domain. This may include array specifiers. A Schema combobox is for user defined datatypes. If domain is based on build-in data type, selecting it does not matter.

Check Not NULL if domain are not allowed to be null. Also you may optionally specify Default value for domain.

Domain name and properties

#### Step 3 - Domain constraints

In this step, you may optionally define CHECK constraints for domain. Start define constraint by typing Constraint name.

Each constraint must be an Expression producing a Boolean result. It should use the name VALUE to refer to the value being tested. Currently, CHECK expressions cannot contain subqueries nor refer to variables other than VALUE.

Press Add button to insert constraint to list. Repeat this procedure for each constraint. Constraint definition may be removed by selecting it and Delete button.

Domain constraints

#### Step 4 - Review SQL statement

In this step, SQL statement for create constraint is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

### Modify Domain

When you select table domain in left tree view and press right mouse button, &kpogre; display menu that run wizards change domain properties

#### Set NOT NULL / Drop NOT NULL

This wizard allow to modify column NOT NULL property, that say, if domain is not allowed to contain null values. Wizard do not need any aditional information from user, SQL statement is displayed in first step.

When you press Next button command will be send to server.

#### Set default

This wizard allow to set or modify domain DEFAULT property for INSERT operation, when value is not specified.

When you press Next button command will be send to server.

#### Drop default

This wizard allow to drop column DEFAULT property for INSERT operation, when value is not specified.

When you press Next button command will be send to server.

### Create Domain constraint

#### Step 1 - Domain constraints

This wizard can create domain CHECK constraints. Start define constraint by typing Constraint name.

Each constraint must be an Expression producing a Boolean result. It should use the name VALUE to refer to the value being tested. Currently, CHECK expressions cannot contain subqueries nor refer to variables other than VALUE.

Press Add button to insert constraint to list. Repeat this procedure for each constraint. Constraint definition may be removed by selecting it and Delete button.

Domain constraints

#### Step 2 - Review SQL statement

In this step, SQL statement for create constraint(s) is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

### Create Function - C language

#### Step 1 - Function name

If you start this wizard when database is selected, choose namespace (AKA schema) for new function. When you run wizard from schema or views folder, actual schema is used.

Type name for new function. The name of the new function must not match any existing function with the same argument types in the same schema. Select function language - C in this case.

Function name

#### Step 2 - Return and arguments types

Choose return type. You may choose any build-in data type, OPAQUE, base or composite type. Optionally, choose function arguments.

Return and arguments types

Using User defined datatype button you may include non-exist datatype to both Data type comboboxes. This is useful, when you create functions for base datatype. Because datatype is not exists while you create functions for it - PostgreSQL will create a "shell" type, which is simply a placeholder entry in the system catalog, and link the input function definition to the shell type. Similarly the other functions will be linked to the (now already existing) shell type. Finally, CREATE TYPE replaces the shell entry with a complete type definition, and the new type can be used.

#### Step 3 - Function properties

In this step, choose function properties.

These attributes inform the system whether it is safe to replace multiple evaluations of the function with a single evaluation, for run-time optimization. Volatile is the default assumption.

- Immutable - indicates that the function always returns the same result when given the same argument values.

- Stable - indicates that within a single table scan the function will consistently return the same result for the same argument values, but that its result could change across SQL statements.

- Volatile - indicates that the function value can change even within a single table scan, so no optimizations can be made.

NULL arguments behavior

- Called on NULL input - (the default) indicates that the function will be called normally when some of its arguments are null. It is then the function author's responsibility to check for null values if necessary and respond appropriately.

- Strict - indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.

Security

- Security invoker - indicates that the function is to be executed with the privileges of the user that calls it. That is the default.

- Security definer - specifies that the function is to be executed with the privileges of the user that created it. Note that you should be careful when creating function with SECURITY DEFINER option. This is potentially security hole.

Step 2 - Function properties

#### Step 4 - Function definition

Enter function library name - the path to an object file. Optionally specify function name inside library. This is C language functions when the function name in the C language source code is not the same as the name of the SQL function.

Step 3 - Function definition

#### Step 5 - Grant execute privileges

This step allow grant execute privileges on function to one or more users or groups of users.

Select users or groups (multiple select allowed) or check PUBLIC for grant to all users.

Step 2 - Grant execute privileges

Click on Add button. One or more grant commands is inserted to list. Grant privilege may be removed from list, using Delete button. After defining all privileges press Next button.

#### Step 6 - Review SQL statement

In this step, SQL statement for create function is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

### Create Function - SQL

#### Step 1 - Function name

If you start this wizard when database is selected, choose namespace (AKA schema) for new function. When you run wizard from schema or views folder, actual schema is used.

Type name for new function. The name of the new function must not match any existing function with the same argument types in the same schema. Select function language - SQL in this case.

Function name

#### Step 2 - Return and arguments types

Choose return type. You may choose any build-in data type, OPAQUE, composite or base type. Check Return a set, if function will return set of return types. Optionally, choose function arguments.

Step 1 - Function name, return and arguments types

#### Step 3 - Function properties

In this step, choose function properties.

These attributes inform the system whether it is safe to replace multiple evaluations of the function with a single evaluation, for run-time optimization. Volatile is the default assumption.

- Immutable - indicates that the function always returns the same result when given the same argument values.

- Stable - indicates that within a single table scan the function will consistently return the same result for the same argument values, but that its result could change across SQL statements.

- Volatile - indicates that the function value can change even within a single table scan, so no optimizations can be made.

NULL arguments behavior

- Called on NULL input - (the default) indicates that the function will be called normally when some of its arguments are null. It is then the function author's responsibility to check for null values if necessary and respond appropriately.

- Strict - indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.

Security

- Security invoker - indicates that the function is to be executed with the privileges of the user that calls it. That is the default.

- Security definer - specifies that the function is to be executed with the privileges of the user that created it. Note that you should be careful when creating function with SECURITY DEFINER option. This is potentially security hole.

Function properties

#### Step 4 - Function definition

EEnter function code here. You must be familiar with choosed language.

Function definition

#### Step 5 - Grant execute privileges

This step allow grant execute privileges on function to one or more users or groups of users.

Select users or groups (multiple select allowed) or check PUBLIC for grant to all users.

Grant execute privileges

Click on Add button. One or more grant commands is inserted to list. Grant privilege may be removed from list, using Delete button. After defining all privileges press Next button.

#### Step 6 - Review SQL statement

In this step, SQL statement for create function is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

### Modify Function

#### Step 1 - Function properties

In this step, choose function properties. This page is the same as in create function wizard.

#### Step 2 - Function definition

Modify function code. You must be familiar with used language.

Step 2 - Function definition

#### Step 3 - Review SQL statement

In this step, SQL statement for replace function is displayed for your check. You may modify it (for example change view name), if you understand, what do you do. Press Next button to perform operation.

### Create Sequence

#### Step 1 - Sequence name and another properties

If you start this wizard when database is selected, choose namespace (AKA schema) for new function. When you run wizard from schema or views folder, actual schema is used.

Type name for new sequence.

The parameter Increment by specifies which value is added to the current sequence value to create a new value. A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1.

The optional Start value allows the sequence to begin anywhere. The default starting value is minvalue for ascending sequences and maxvalue for descending ones.

Optionally you may choose Minimum value and Maximum value for sequence.

The optional parameter Cache specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The minimum value is 1 (only one value can be generated at a time, i.e., no cache), and this is also the default.

The Cycle option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively.

Step 1 - Sequence name and another properties

#### Step 2 - Review SQL statement

In this step, SQL statement for create sequence is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

### Modify Sequence

#### Step 1 - Sequence properties

The parameter Increment by specifies which value is added to the current sequence value to create a new value. A positive value will make an ascending sequence, a negative one a descending sequence.

Modify Last value if you want to change the current value of the sequence.

You may change Minimum value or Maximum value for sequence. Leave it blank, if you want to reset it to default values.

The parameter Cache specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The minimum value is 1 (only one value can be generated at a time, i.e., no cache), and this is also the default.

The Cycle option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively.

Step 1 - Sequence properties

#### Step 2 - Review SQL statement

In this step, SQL statement for alter sequence is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

### Create Table

#### Step 1 - Table name

If you start this wizard when database is selected, choose schema for new table. When you run wizard from schema or tables folder, actual schema is used.

Type name for new table. The Tablespace is the name of the tablespace in which the new table is to be created. If not supplied, the default tablespace of the table's schema will be used. If you need new table without OIDs, choose this option.

#### Step 2 - Define columns

In this step, user define list of table columns. Each column have name, datatype and another optional properties.

First, type column name. This name must be unique in table. Next choose datatype from combo box. Short description is displayed in line under combobox. If you can create multidimensional column, choose number of dimensions greather than 0. Some variable lenght datatypes requires size. And some datatypes requires precision and scale.

Datatype can be build-in type, or domain or user defined base type. If you want to use domain or type from different namespace, use Schema combobox to list them.

After entering basic information, user may select table constraints for column:

- NOT NULL - The column is not allowed to contain null values.

- UNIQUE - Specifies that a column may contain only unique values.

- DEFAULT - Default value for INSERT operation, when value is not specified. Use Quoted checkbox for setting if default value is text, or leave it unchecked if default is function, e.g. now(). For simply set current datetime value as default, use Now button.

- CHECK - The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an INSERT or UPDATE operation to succeed.

When you have all information for new column, press Add button. Column may be removed from list and user may change order of columns. After defining all columns press Next button.

#### Step 3 - Define table constraints

Create constraints is optional. Table may have one (and only one) primary key, and zero or more of foreign keys. Create primary key is good practice. Select type of new constraint in combo box.

##### Primary key

Select column in Available columns list and press Add. If you need multicolumn primary key, repeat this step for another columns. After adding first column, primary key name is automatically filled from table name. You may change this name.

Optionally select Tablespace in which the index associated with a PRIMARY KEY constraint will be created. If not supplied, the index will be created in the same tablespace as the table.

Then, press Add button in Table constraints group box.

##### Foreign key

First select referenced table and their unique. This may be primary key or unique index. Then select column in Available columns list and press Add button.

Columns in FK columns and in Referenced columns must be compatible regards datatypes, and columns number must match.

User may specify another optional properties for foreign key. See at PostgreSQL documentation, if you don't understand those parameters.

Then, press Add button in Table constraints group box.

##### Unique

Select column in Available columns list and press Add. If you need multicolumn unique, repeat this step for another columns. After adding first column, unique name is automatically filled from table name. You may change this name.

Optionally select Tablespace in which the index associated with a UNIQUE constraint will be created. If not supplied, the index will be created in the same tablespace as the table.

Then, press Add button in Table constraints group box.

##### Check

Type an Expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or UNKNOWN succeed. Should any row of an insert or update operation produce a FALSE result an error exception is raised and the insert or update does not alter the database. A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint may reference multiple columns.

If you doubleclick on column in Available columns list, it is inserted to Expression edit line to current cursor position.

Then, press Add button in Table constraints group box.

#### Step 4 - Define table indexes

Create indexes is optional. Indexes are primarily used to enhance database performance.

Select column in Available columns list and press Add button. If you need multicolumn index, repeat this step for another columns. After adding first column, index name is automatically filled from table and first column name. You may change this name.

If you create unique index, check this option.

Optionally select Tablespace in which the index will be created. If not supplied, the index will be created in the same tablespace as the table.

Then, press Add button in Table indexes group box.

##### Expression Index

An index field can be an expression computed from the values of one or more columns of the table row. This feature can be used to obtain fast access to data based on some transformation of the basic data.

For example, an index computed on upper(first_name) would allow the clause WHERE upper(first_name) = 'JIM' to use an index. Select this function in combobox.

Index functions combobox is refreshed, each time is Index columns list changed. Listed are only "immutable" functions with number of parameters as index columns count.

##### Partial Index

When the WHERE clause is present, a partial index is created.

Type WHERE clause that define partial data for index.

Index may be removed from list, using Delete button. After defining all indexes press Next button.

#### Step 5 - Define user rights

This step allow grant specific privileges on table to one or more users or groups of users. These privileges are added to those already granted, if any.

Select users or groups (multiple select allowed) or check PUBLIC for grant to all users.

The possible privileges are:

- SELECT - Allows SELECT from any column of the table. Also allows the use of COPY TO.

- INSERT - Allows INSERT of a new row into the specified table. Also allows COPY FROM.

- UPDATE - Allows UPDATE of any column of the specified table. SELECT ... FOR UPDATE also requires this privilege.

- DELETE - Allows DELETE of a row from the specified table.

- RULE - Allows the creation of a rule on the table/view.

- REFERENCES - To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced tables.

- TRIGGER - Allows the creation of a trigger on the specified table.

Check one or more privileges, or ALL.

Click on Add button. One or more grant commands is inserted to list. Grant privilege may be removed from list, using Delete button. After defining all privileges press Next button.

#### Step 6 - Review SQL statement

In this step, SQL statement for create table, indexes and grant privileges is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 7 - Check operation result

Finally, check operation result. If you get any error, you may go back and try make changes and retry step 6.

### Analyze table

#### Step 1 - Analyze table parameters

Analyze collects statistics about the contents of table, and stores the results in the system table pg_statistic. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.

Optionally enable Verbose that enables display of progress messages.

Optionally specify one or more columns to analyze. Defaults to All columns.

Then, press Next button.

#### Step 2 - Review SQL statement

In this step, SQL statement for analyze. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 3 - Check operation result

Finally, check operation result.

### Vacuum table

#### Step 1 - Vacuum table properties

Vacuum reclaims storage occupied by deleted tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.

Enabling Full vacuum may reclaim more space, but takes much longer and exclusively locks the table.

Enable Freeze to aggressive "freezing" of tuples.

Optionally enable Verbose that enables display of progress messages.

Enable Also perform Analyze to updates statistics used by the planner to determine the most efficient way to execute a query. Optionally specify one or more columns to analyze. Defaults to All columns.

Then, press Next button.

#### Step 2 - Review SQL statement

In this step, review SQL statement for vacuum. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 3 - Check operation result

Finally, check operation result.

### Reindex table

#### Step 1 - Review SQL statement

Reindex rebuilds an index based on the data stored in the table, replacing the old copy of the index. There are two main reasons to use:

- An index has become corrupted, and no longer contains valid data. Although in theory this should never happen, in practice indexes may become corrupted due to software bugs or hardware failures. REINDEX provides a recovery method.

- The index in question contains a lot of dead index pages that are not being reclaimed. This can occur with B-tree indexes in PostgreSQL under certain access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages.

#### Step 2 - Check operation result

Check operation result.

### Set cluster for table

#### Step 1 - Select index for cluster

Set cluster selects the default index for future Cluster operations. It does not actually re-cluster the table.

Choose index that become default index for future CLUSTER operations.

Then, press Next button.

#### Step 2 - Review SQL statement

In this step, review SQL statement for re-cluster table. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 3 - Check operation result

Finally, check operation result.

### Set without cluster

#### Step 1 - Review SQL statement

This operation removes the most recently used Cluster index specification from the table. This affects future cluster operations that don't specify an index.

Choose index that become default index for future CLUSTER operations.

Press Next button.

#### Step 2 - Check operation result

Check operation result.

### Cluster table

#### Step 1 - Review SQL statement

This operation cluster a table according to an index. When a table is clustered, it is physically reordered based on the index information.

When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired on it. This prevents any other database operations (both reads and writes) from operating on the table until the CLUSTER is finished.

Press Next button.

#### Step 2 - Check operation result

Check operation result.

### Copy table to file

#### Step 1 - Copy to file parameters

This operations copies the contents of a table to a file.

Specify the absolute path name of the output file. Notify that this is server side path. If PostgreSQL and &kpogre; not run on the same machine, using path dialog button may produce unexistent path. Output file must be accessible with write privileges by the PostgreSQL user (the user ID the server runs as).

If no column list is specified, All columns will be used. An optional list of columns to be copied.

Check CSV mode option to enable Comma Separated Value mode.

Then, press Next button.

#### Step 2 - Copy file parameters

In this step, specify aditional parameters for copy operation.

Binary causes all data to be stored or read in binary format rather than as text.

OIDs Specifies copying the OID for each row.

Delimiter is the single character that separates columns within each row (line) of the file. The default is a tab character in text mode, a comma in CSV mode..

NULL string - the string that represents a NULL value. The default is \N (backslash-N) in text mode, and a empty value with no quotes in CSV mode.

Quote specifies the quotation character in CSV mode. The default is double-quote.

Escape specifies the character that should appear before a QUOTE data character value in CSV mode. The default is the Quote value (usually double-quote).

#### Step 3 - Force quote columns

In CSV COPY TO mode, forces quoting to be used for all non-NULL values in each specified column. NULL output is never quoted.

#### Step 4 - Review SQL statement

In this step, SQL statement for copy to. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 5 - Check operation result

Finally, check operation result.

### Copy table from file

#### Step 1 - Copy to file parameters

This operations copies the contents of a table to a file.

Specify the absolute path name of the input file. Notify that this is server side path. If PostgreSQL and &kpogre; not run on the same machine, using path dialog button may produce unexistent path.

If no column list is specified, All columns will be used. An optional list of columns to be copied.

Check CSV mode option to enable Comma Separated Value mode.

Then, press Next button.

#### Step 2 - Copy file parameters

In this step, specify aditional parameters for copy operation.

Binary causes all data to be stored or read in binary format rather than as text.

OIDs Specifies copying the OID for each row.

Delimiter is the single character that separates columns within each row (line) of the file. The default is a tab character in text mode, a comma in CSV mode..

NULL string - the string that represents a NULL value. The default is \N (backslash-N) in text mode, and a empty value with no quotes in CSV mode.

Quote specifies the quotation character in CSV mode. The default is double-quote.

Escape specifies the character that should appear before a QUOTE data character value in CSV mode. The default is the Quote value (usually double-quote).

#### Step 3 - Force NOT NULL columns

In CSV COPY FROM mode, process each specified column as though it were quoted and hance not a NULL value. For the default null string in CSV mode (''), this causes a missing values to be input as a zero-length strings.

#### Step 4 - Review SQL statement

In this step, SQL statement for copy from. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 5 - Check operation result

Finally, check operation result.

### Set table tablespace

#### Step 1 - Review SQL statement

This operation changes the Tablespace of the table.

Choose new tablespace for table.

Press Next button.

#### Step 2 - Review SQL statement

In this step, SQL statement for set new tablespace. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 3 - Check operation result

Check operation result.

### Set without OIDs

#### Step 1 - Review SQL statement

This operation removes the oid system column from the table. This is exactly equivalent to DROP COLUMN oid RESTRICT, except that it will not complain if there is already no oid column.

Note that there is no variant of ALTER TABLE that allows OIDs to be restored to a table once they have been removed.

#### Step 2 - Check operation result

Check operation result.

### Create Table Column

#### Step 1 - Define table column(s)

This wizard allow to add one or more columns to table. Each column have name, datatype and another optional properties.

First, type column name. This name must be unique in table. Next choose datatype from combo box. Short description is displayed in line under combobox. If you can create multidimensional column, choose number of dimensions greather than 0. Some variable lenght datatypes requires size. And some datatypes requires precision and scale.

After entering basic information, user may select table constraints for column:

- NOT NULL - The column is not allowed to contain null values.

- UNIQUE - Specifies that a column may contain only unique values.

- DEFAULT - Default value for INSERT operation, when value is not specified.

- CHECK - The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an INSERT or UPDATE operation to succeed.

When you have all information for new column, press Add button. Column may be removed from list and user may change order of columns. After defining all columns press Next button.

#### Step 2 - Review SQL statement

In this step, SQL statement for create columns is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 3 - Check operation result

Finally, check operation result. If you get any error, you may go back and try make changes and retry step 1.

### Modify Table Column

When you select table column in left tree view and press right mouse button, &kpogre; display menu that run wizards for drop column, rename it or change column properties.

#### Set NOT NULL / Drop NOT NULL

This wizard allow to modify column NOT NULL property, that say, if column is not allowed to contain null values. Wizard do not need any aditional information from user, SQL statement is displayed in first step.

When you press Next button command will be send to server.

#### Set default

This wizard allow to set or modify column DEFAULT property for INSERT operation, when value is not specified.

When you press Next button command will be send to server.

#### Drop default

This wizard allow to drop column DEFAULT property for INSERT operation, when value is not specified.

When you press Next button command will be send to server.

#### Set type

This wizard allow to change column datatype. Indexes and simple table constraints involving the column will be automatically converted to use the new column type by reparsing the originally supplied expression. The optional Using expression specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A Using expression clause must be provided if there is no implicit or assignment cast from old to new type.

When you press Next button command will be send to server.

#### Set storage

This wizard sets the storage mode for a column. This controls whether this column is held inline or in a supplementary table, and whether the data should be compressed or not. PLAIN must be used for fixed-length values such as integer and is inline, uncompressed. MAIN is for inline, compressible data. EXTERNAL is for external, uncompressed data, and EXTENDED is for external, compressed data. EXTENDED is the default for all data types that support it. The use of EXTERNAL will, for example, make substring operations on a text column faster, at the penalty of increased storage space.

When you press Next button command will be send to server.

#### Set statistics

This wizard allow to set the per-column statistics-gathering target for subsequent ANALYZE operations. The value can be set in the range 0 to 1000; alternatively, set it to -1 to revert to using the system default statistics target.

When you press Next button command will be send to server.

### Create Table Constraint

#### Step 1 - Define table constraint

Select type of new constraint in combo box. This wizard allow to create one or more constraint.

##### Primary key

Select column in Available columns list and press Add. If you need multicolumn primary key, repeat this step for another columns. After adding first column, primary key name is automatically filled from table name. You may change this name.

Optionally select Tablespace in which the index associated with a PRIMARY KEY constraint will be created. If not supplied, the index will be created in the same tablespace as the table.

Then, press Add button in Table constraints group box.

##### Foreign key

First select referenced table and their unique. This may be primary key or unique index. Then select column in Available columns list and press Add button.

Columns in FK columns and in Referenced columns must be compatible regards datatypes, and columns number must match.

User may specify another optional properties for foreign key. See at PostgreSQL documentation, if you don't understand those parameters.

Then, press Add button in Table constraints group box.

##### Unique

Select column in Available columns list and press Add. If you need multicolumn unique, repeat this step for another columns. After adding first column, unique name is automatically filled from table name. You may change this name.

Optionally select Tablespace in which the index associated with a UNIQUE constraint will be created. If not supplied, the index will be created in the same tablespace as the table.

Then, press Add button in Table constraints group box.

##### Check

Type an Expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or UNKNOWN succeed. Should any row of an insert or update operation produce a FALSE result an error exception is raised and the insert or update does not alter the database. A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint may reference multiple columns.

If you doubleclick on column in Available columns list, it is inserted to Expression edit line to current cursor position.

Then, press Add button in Table constraints group box.

#### Step 2 - Review SQL statement

In this step, SQL statement for create constratints is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 3 - Check operation result

Finally, check operation result. If you get any error, you may go back and try make changes and retry step 1.

### Create Table Index

#### Step 1 - Define table indexes

Indexes are primarily used to enhance database performance.

Select column in Available columns list and press Add button. If you need multicolumn index, repeat this step for another columns. After adding first column, index name is automatically filled from table and first column name. You may change this name.

If you create unique index, check this option.

Optionally select Tablespace in which the index will be created. If not supplied, the index will be created in the same tablespace as the table.

Then, press Add button in Table indexes group box.

See at Create table Wizard if you need know how to create partial or expression index.

#### Step 2 - Review SQL statement

In this step, SQL statement for create indexes is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 3 - Check operation result

Finally, check operation result. If you get any error, you may go back and try make changes and retry step 1.

### Reindex index

#### Step 1 - Review SQL statement

Reindex rebuilds an index based on the data stored in the table, replacing the old copy of the index. There are two main reasons to use:

- An index has become corrupted, and no longer contains valid data. Although in theory this should never happen, in practice indexes may become corrupted due to software bugs or hardware failures. REINDEX provides a recovery method.

- The index in question contains a lot of dead index pages that are not being reclaimed. This can occur with B-tree indexes in PostgreSQL under certain access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages.

#### Step 2 - Check operation result

Check operation result.

### Set index tablespace

#### Step 1 - Review SQL statement

This operation changes the Tablespace of the index.

Choose new tablespace for index.

Press Next button.

#### Step 2 - Review SQL statement

In this step, SQL statement for set new tablespace. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 3 - Check operation result

Check operation result.

### Create Table Rule

#### Step 1 - Rule name and properties

Rules is used to perform additional commands to be executed when a given command on a table is executed.

Type rule name and choose an event for rule.

Optionally specify WHERE condition. The condition expression may not refer to any tables except NEW and OLD, and may not contain aggregate functions.

Then choose, what rule do:

- Also - indicates that the command should be executed in addition to the original command. .

- Instead - indicates that the command should be executed instead of the original command.

- Nothing - indicates that original command will not be executed.

For Also and Instead rule specify command that make up the rule action. Valid commands are SELECT, INSERT, UPDATE, DELETE, or NOTIFY.

Then, press Next button.

#### Step 2 - Review SQL statement

In this step, SQL statement for create rule is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 3 - Check operation result

Finally, check operation result. If you get any error, you may go back and try make changes and retry step 1.

### Create Table Trigger

#### Step 1 - Trigger name and properties

The trigger can be specified to fire either before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE, or DELETE is attempted) or after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed). If the trigger fires before the event, the trigger may skip the operation for the current row, or change the row being inserted (for INSERT and UPDATE operations only). If the trigger fires after the event, all changes, including the last insertion, update, or deletion, are "visible" to the trigger.

Type trigger name and choose one or more events for trigger.

Choose trigger function. Listed are only functions from current namespace, without arguments, that returns datatype OPAQUE for PostgreSQL 7.x or older, or datatype TRIGGER for PostgreSQL 8 or newer.

Also specify whether the trigger procedure should be fired once for every row affected by the trigger event, or just once per SQL statement.

Choose if the function is called before or after the event.

Then, press Next button.

#### Step 2 - Review SQL statement

In this step, SQL statement for create trigger is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

#### Step 3 - Check operation result

Finally, check operation result. If you get any error, you may go back and try make changes and retry step 1.

### Create Base type

#### Step 1 - Type name

This wizard registers a new base type (scalar type) for use in the current data base. The user who defines a type becomes its owner.

You must register two or more functions before defining the type. The support functions input_function and output_function are required, while the functions receive_function, send_function and analyze_function are optional. Generally these functions have to be coded in C or another low-level language.

If you start this wizard when database is selected, choose namespace (AKA schema) for new type. When you run wizard from schema or views folder, actual schema is used.

Enter name for new type. The type name must be distinct from the name of any existing type or domain in the same schema. (Because tables have associated data types, the type name must also be distinct from the name of any existing table in the same schema.)

Type name

#### Step 2 - Define functions

Choose input and output functions for datatype. Optionally choose send, receive and analyze functions. You may choose Schema for those functions.

Domain name and properties

#### Step 3 - Define type properties

In this step, you may set optional properties for datatype.

Internal length - a numeric constant that specifies the length in bytes of the new type's internal representation. The default assumption is that it is variable-length.

Alignment - The storage alignment requirement of the data type. If specified, it must be char, int2, int4, or double; the default is int4.

Storage - The storage strategy for the data type. If specified, must be plain, external, extended, or main; the default is plain.

Default value - The default value for the data type. If this is omitted, the default is null. Use Quoted checkbox for setting if default value is text, or leave it unchecked if default is function, e.g. now(). For simply set current datetime value as default, use Now button.

Element - The type being created is an array; this specifies the type of the array elements.

Delimiter - The delimiter character to be used between values in arrays made of this type.

Domain constraints

#### Step 4 - Review SQL statement

In this step, SQL statement for create type is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

### Create Composite type

#### Step 1 - Type name

This wizard registers a new composite type for use in the current data base. The user who defines a type becomes its owner.

The composite type is specified by a list of attribute names and data types.

If you start this wizard when database is selected, choose namespace (AKA schema) for new type. When you run wizard from schema or views folder, actual schema is used.

Enter name for new type. The type name must be distinct from the name of any existing type or domain in the same schema. (Because tables have associated data types, the type name must also be distinct from the name of any existing table in the same schema.)

Type name

#### Step 2 - Define attributes

In this step, user define list of type attributes. Each attribute have name, datatype and another optional properties.

First, type attribute name. This name must be unique in type. Next choose datatype from combo box. Some datatypes requires precision and scale.

Datatype can be build-in type, or user defined base type. If you want to use type from different namespace, use Schema combobox to list them.

Domain name and properties

#### Step 3 - Review SQL statement

In this step, SQL statement for create type is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

### Create View

#### Step 1 - View name and used tables

If you start this wizard when database is selected, choose namespace (AKA schema) for new view. When you run wizard from schema or views folder, actual schema is used.

Type name for new view. Add tables that will be used for output columns and joins. Using one or two letters alias for each table is good practice.

#### Step 2 - Define columns

For each table you may add column to view output. If you want to rename column, use AS field.

#### Step 3 - Define joins

If you have more than one table in view, you probably need define joins. Choose Join type. Select tables for joins and also select columns. Selecting more than one column is allowed. Joined columns must have compatible datatypes and selected columns count must match.

Press Add button in Joins group box. Join definition is included to list. You may repeat this for all needed joins in view.

After adding first join, left table is locked and you may change only right table.

Join may be removed from list, using Delete button. After defining all joins press Next button.

#### Step 4 - Define conditions

In this step you may define conditions to reduce number of output rows. Thi step is optional. Select Table and its Column for condition. Also select Condition type and enter Value.

Press Add button in Conditions group box. Condition is included to list. You may repeat this for all needed conditions in view.

Condition may be removed from list, using Delete button. After defining all conditions press Next button.

#### Step 5 - Review SQL statement

In this step, SQL statement for create view is displayed for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

### Modify View

#### Step 1 - Enter SQL statement for view

Modify view SQL statement. Use Test SQL button to testing it. Then press Next button.

Modify View

#### Step 2 - Review SQL statement

In this step, SQL statement for replace view is displayed for your check. You may modify it (for example change view name), if you understand, what do you do. Press Next button to perform operation.

## The Menu Entries

### The File Menu

Ctrl O File Open

  This command opens a SQL file. It does this by means of a dialog box which allows the user to navigate the file system. The dialog operates like a small file manager. Clicking on directories displayed in the central window directs the dialog to enter that directory - displaying its contents. There is an entry/dropdown box which can be used to type in directly the location and name of the file or by clicking the arrow at the side choose from a dropdown list of recently used locations. Below this is a filter which similarly may have data entered directly or chosen from a dropdown list of recent filter types. The filter facility lets only files that meet its specifications be displayed in the central window. If the filter contained text such as `*.sql` then only files with the txt extension would be visible in the selection window. Below the filter is a status bar giving information about the number of files and subdirectories within the current directory.

  The toolbar, which is located at the top of the dialog, has left and right arrow buttons that let the user move back and forth through previously selected directories as well as an up arrow button for moving up the directory tree. The button with the little house takes the user to his or her home directory and the one with the two arrows curved in on each other updates the view of the current directory. The flag button lets the user set a new bookmark at the current directory or go to one that was previously set. The last button on the toolbar allows you to create a new directory, and finally there is dropdown box with a list of some commonly frequented directories.

File OpenRecent

  This is a shortcut to open recently saved SQL files. Clicking on this item opens a list to the side of the menu with several of the most recently saved files. Clicking on a specific file will open it in &kpogre; - if the file still resides at the same location.

Ctrl S File Save

  This saves the current SQL file. If there has already been a save of the document then this will overwrite the previously saved file without asking for the user's consent. If it is the first save of a new document the save as dialog (described below) will be invoked.

File Save As

  This allows a SQL query to be saved with a new file name. This is done by means of the file dialog box described above in the Open section of this help file.

File Export Result

  This allow export query result to CSV (comma separated values) file.

File Print

  This allow print document. Not implemented yet.

Ctrl Q File Quit

  Quits &kpogre;

### The Edit Menu

Ctrl Z Edit Undo

  This is used to eliminate or reverse the most recent user action or operation.

Ctrl Shift Z Edit Redo

  This will reverse the most recent change (if any) made using Undo

Ctrl X Edit Cut

  This command deletes the current selection and places it on the clipboard. The clipboard is a feature of KDE that works invisibly to provide a way to transfer data between applications.

Ctrl C Edit Copy

  This copies the currently selected text to the clipboard so that it may be pasted elsewhere. The clipboard is a feature of KDE that works invisibly to provide a way to transfer data between applications.

Ctrl V Edit Paste

  This will insert the contents of the clipboard at the cursor position. The clipboard is feature of KDE that works invisibly to provide a way to transfer data between applications.

Edit Clear Inputarea

  This clear SQL query editor contents

Ctrl F Edit Find

  This opens the find dialog which is used to specify the Text to Find in the object tree. You may find object by names or OID's.

F3 Edit Find Next

  This repeats the last find operation, if any, without calling the find dialog box.

Ctrl E Edit Deselect item

  This unselect item in tree window

### The View Menu

View To Parent item

  Go up a level in the tree hierarchy. Select parent item.

View To Back item

  Used for browsing using OID links. Return back to previously selected item.

View To Forward item

  Used for browsing using OID links. Go forward to next item.

### The Database Menu

Database Connect

  Display dialog where is host name, port, username and password. Allow establish new connetion to PostgreSQL server.

Database Disconnect

  Close connection to selected PostgreSQL server.

F8 Database Run query

  Send query from selected query/result windows to PostgreSQL server. Display query result.

### The Settings Menu

Settings Show Toolbar

  When checked, this displays a movable toolbar containing buttons used to initiate frequently used commands. When unchecked the toolbar is hidden.

Settings Show Statusbar

  When checked, this displays a small bar at the bottom of the main window containing information about the status of the current document. When unchecked the statusbar is hidden.

Settings Configure Shortcuts

  This command opens a dialog box whereby the key bindings may be changed. A display window at the top of the dialog box shows the list of commands (actions) that can have keyboard shortcuts. Below the display are three radio buttons. The user may choose between No Key, Default Key, and Custom Key. (Note that a set of radio buttons only allows the selection of one of the offered items - in the way that buttons on a car radio only offer the selection of one preset station. Also, the Default Key selection is only available for those commands that actually have a 'default' shortcut.) Selecting the Custom Key option activates the three check boxes and key button at the bottom of the dialog. The user may then select a key combination for the command in question by means of the check boxes and key button. For example, with the About KDE command selected in the display window, the user could select Ctrl and Alt, click on the key button, and then press the K key on the keyboard. This would mean that anytime he or she held down the Ctrl and Alt buttons and pressed K (while using &kpogre;) the About KDE display box would be called.

Settings Configure Toolbars

  This will open the dialog whereby the toolbars configuration may be changed. The user can choose which shortcut buttons should appear on the toolbar. A display window on the left lists the commands available to placed on the toolbar. A display on the right lists those commands already on the toolbar. A set of four arrow buttons between the two displays manipulates the selections. The right pointing arrow places any command selected in the left pane onto the right pane, i.e., it is added to the toolbar. The left arrow does just the opposite, removing any action selected in the right window from the toolbar. The up and down pointing arrows change the position of an action selected in the right window which changes the position of its button in the toolbar.

Settings Configure &kpogre;

  This menu item opens a dialog whereby several different settings may be adjusted.

### The Help Menu

F1 Help Contents

  This invokes the KDE help system starting at the &kpogre; help pages (this document).

Shift F1 Help What's This?

  This changes the mouse cursor to a combination arrow and question mark. Clicking on items within &kpogre; with this arrow will open a help window (if one exists for the particular item) explaining the item's function.

Help Report Bug

  This calls a dialog box to help the user help the KDE team to track down and solve any problems(bugs) in the program. The dialog attempts to do this by means of email using the information given by the user.

Help About &kpogre;

  This will display version and author information.

Help About KDE

  This displays the KDE version and other basic information.

## Configure &kpogre;

Selecting Settings Configure &kpogre; from the menu brings up the Configure-&kpogre; dialog box. This dialog can be used to alter application settings. By means of four buttons along the bottom of the box the user can control the process. She or he may invoke the Help system, accept the current settings pressing Apply button, accept the current settings and close the dialog by means of the OK button, or Cancel the process.

### General

This category allow set general &kpogre; behavior.

Automatically load system schemas

  When checked, &kpogre; automatically load object from system schema, while opening database. You may speed-up opening database by uncheck this option. But remember that in this case the OID links will not works to unloaded system objects.

Automatically load user schemas

  When checked, &kpogre; automatically load object from all user schemas, while opening database. You may speed-up opening database by uncheck this option. Schemas will be loaded later when you open it.

Use $$ for functions body definition

  When checked, Create funtion wizard use $$ around function body instead of apostrophes.

### SQL Editor

This category allow set SQL editor font and syntax highlighting. This setting is aplicable for simple SQL editor, used in final Wizards page. To configure KatePart editor in Query/result window, use Settings -> Configure Editor

SQL Editor

Colors

  You may choose various colors for different SQL syntax.

Font

  You may choose font used by SQL editor.

### Datatable

This category allow set Datatable behavior.

Confirm deleting row

  When checked, every deleting row must be confirmed by dialog.

LOB buffer size [MB]:

  For load and save large objects (text and bytea fields), temporary buffer must be allocated. Set reasonable anmount of memory in megabytes for this buffer - depends from size of files stored to text and bytea fields.

## Credits and License

&kapp; Program copyright 2001-2008 the &kapp; developers:

Lumir Vanek lvanek@users.sourceforge.net

  Developer and maintainer

Dominik Sacher dsacher@users.sourceforge.net

  Former developer

Documentation copyright 2004-2008 Lumir Vanek lvanek@users.sourceforge.net

&underFDL;

&underGPL;

## Installation

### How to obtain &kpogre;

You may obtain &kpogre; from URL: http://sourceforge.net/projects/kpogre.

### Requirements

In order to successfully use &kpogre;, you need KDE 3.5.x or higher. For instructions on acquiring &kde; please see http://www.kde.org.

This application requires libpqxx library. You may obtain it from libpqxx home page.

### Compilation and Installation

In order to compile and install &kpogre; on your system, type the following in the base directory:

    % ./configure
    % make all
    % make install

Since &kpogre; uses autoconf and automake you should have not trouble compiling it. Should you run into problems please report them to the KDE mailing lists.

&documentation.index;