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.