Tutorial 8 - Querying database

This tutorial show, how to use SQL Query / result window to perform SQL queries to get results.

Opening SQL Query / result window

Choose File - New menu item to open SQL Query / result window.

KPoGre shows Query / result window with SQL input area and result area. Also toolbar is displayied. Tab icons in tabs may have three colors:

• Query / result window is ready to enter SQL
• SQL was sent to server and user waits for result
• Result is displayied and query is not changed after run last query

SQL query

SQL query can be saved and opened using standard menu entries in File menu. From version 1.6.2, KPoGre uses KatePart editor for editing SQL's, - editing is similar as in Kate, KWrite, KDevelop or others Kate based applications.

Ctrl+Space completion

While editing, code completion box can be showed by pressing Ctrl+Space. It contains SQL keywords, tables, views and functions listed here. Code completion is case sensitive - to list SQL keywords, start type them in UPPER case letters.

Period completion

When your press period after table, or function returning set name (or it's alias), code completion box is showed, with listed columns.

Note: Code completion is not finished yet, at this time, it not works well for another than public schema. But it's suitable for most of common situations.

KatePart editor can be configured using menu entry Setting - Configure Editor.

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 Database -> 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 SQL statement using Database -> Stop query menu item or pressing toolbar button.

Options

Pressing Options button you 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 - Statement 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 displayied 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 Database -> Fetch next and Database -> Fetch all.

Saving and exporting result

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

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