Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Using Queries

As should be expected, you are able to design, edit, and run queries through PgAccess. Click on the Queries tab to view a list of the defined queries associated with your database. This area of the program should be familiar to Microsoft Access users, as the visual query designer and other features are very similar to their counterparts within that program.

To create a new query, click the New button. This will open the "Query builder" window. Before designing the query, you should name it with the Query name field. This name is arbitrary and serves no function within the query; it is needed only so that PgAccess has something to display for this query in the list of available queries. You may also add comments in the comment window at this point.

Manually designing a query

After naming the query, you can either design it manually or use the visual designer tool to speed up the process. To manually design the query, use the large, white box below the Query name field to type in the SELECT statement that will be used to query the database. You can spread this statement out over multiple lines, if you wish.

Using the visual designer

To use the visual designer tool for creation of the new query, click on the "Visual designer" button. As stated before, the interface to this tool is similar to the query designer tool in Microsoft Access. You are initially given a blank canvas to work with. Add tables to the canvas by typing the name of the table in the Add table field (the cursor enters text into this field by default). Alternatively, you can add tables by clicking the down-arrow button and selecting the table you wish to add from its list of available tables.

Once you've added the tables you wish to use, you can form links between them by clicking and dragging on a field, then pulling it from one table object to the other. When a link is formed it will display as a thin line that connects the two objects together. Note that you can move table objects around the canvas and the link graphic will stretch to fit whatever arrangement you desire. You may delete tables from the canvas by clicking on their labels and hitting the Delete key on your keyboard. Similarly, links may be deleted between columns by clicking on them and pressing the Delete key.

Any links between corresponding table columns will be translated into a SQL WHERE clause, specifying conditions upon which to join two table sets. A link will only represent a condition involving the equal-to operator (=). If you require a different condition, the SQL statement can be edited manually in the "Query builder" window; bear in mind that going back to the Visual Designer will cause any modified relationship to be re-created as an equal-to relationship when it is saved.

Figure 6-4 shows the PgAccess Visual Designer interface. It illustrates a fairly involved SQL query, reproduced in a more comprehensible, graphical form.

Figure 6-4. The PgAccess Visual query designer

To select fields that you wish to be included in the results of the query, drag the field name down into the result zone (the cell-divided area at the bottom of the screen). You may define conditions you wish to be applied to results from the query; do this by entering a condition into the Criteria field. To see the SQL statement you have created with the visual design, click the "Show SQL" button. To execute your query (for testing purposes), click the "Execute SQL" button. When you are done creating the query's design, click on the "Save to query builder" button. This saves the query within the pga_queries table.

Executing a query

To view the results of an existing query, click the Queries tab, select the desired query from the list in the main PgAccess window, and click the Open button. This displays the retrieved rows in a window similar to the window used for modifying tables, though this table is read-only. You can use the Sort field to sort the records by an expression, or the Filter conditions field to provide a filter expression.

Modifying a query

To modify an existing query, click the "Queries" tab, select it from the query list in the main PgAccess window, and click the Design button. This will display the "Query builder" window, which is the same view as if you were to create a new query. The query's name and SQL statement will be displayed in the window, as well as any comments you added onto it when it was originally designed. From here you can either edit the SQL statement directly or use the visual designer.

Remember to click the "Save query definition" button to save your modifications to an existing query.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire