5.4. Example - The Different Ways to Get Query Results

pgtcl has many different ways to get at query results. The purpose of this example is to clarify the different ways the query result data can be returned.

Assume the following data table called volcanos:

(id INTEGER PRIMARY KEY,volcano TEXT,location TEXT,last_eruption DATE)
1Mount KarangetangIndonesia2001-01-25

Now assume the following query was issued:

set result [pg_exec $conn "SELECT * FROM volcanos ORDER BY ID"]

The following examples illustrate the different ways to access the data.

Example 5.5. Result data: -getTuple

# pg_result -getTuple returns a Tcl list containing the result column values
# from a single row (tuple), selected by a zero-based row number argument.
puts "Result is: [pg_result $result -getTuple 0]


Result is: 1 {Mount Karangetang} Indonesia 2001-01-25

Example 5.6. Result data: -tupleArray

# pg_result -tupleArray assigns values from one row of the query to an array.
# The desired row is selected by a zero-based row number argument.
# The array indexes are the column names.
pg_result $result -tupleArray 2 MyArray
parray MyArray


MyArray(id)            = 3
MyArray(last_eruption) = 1996-03-10
MyArray(location)      = Alaska
MyArray(volcano)       = Akutan

Example 5.7. Result data: -assign

# pg_result -assign assigns the entire query result - column values from
# all rows - to a single array, indexed by the row number (starting at zero),
# and the column name, with the parts of the array index separated by a comma.
pg_result $result -assign MyArray
parray MyArray


MyArray(0,id)            = 1
MyArray(0,last_eruption) = 2001-01-25
MyArray(0,location)      = Indonesia
MyArray(0,volcano)       = Mount Karangetang
MyArray(1,id)            = 2
MyArray(1,last_eruption) = 1997-07-12
MyArray(1,location)      = Japan
MyArray(1,volcano)       = Hakkoda
MyArray(2,id)            = 3
MyArray(2,last_eruption) = 1996-03-10
MyArray(2,location)      = Alaska
MyArray(2,volcano)       = Akutan
MyArray(3,id)            = 4
MyArray(3,last_eruption) = 1996-04-04
MyArray(3,location)      = Nicaragua
MyArray(3,volcano)       = Momotombo

Example 5.8. Result data: -list

# pg_result -list returns a Tcl list containing the entire query result.
# The list items are in row-major, column-minor order.
puts "Result is: [pg_result $result -list]

Output (line-wrapped for presentation):

Result is: 1 {Mount Karangetang} Indonesia 2001-01-25 2 Hakkoda Japan
  1997-07-12 3 Akutan Alaska 1996-03-10 4 Momotombo Nicaragua 1996-04-04

Example 5.9. Result data: -llist

# pg_result -llist returns a Tcl list of lists containing the entire query
# result.  Each item in the outer list is for one row of the query result,
# and each inner list contains the column values for that row.
puts "Result is: [pg_result $result -llist]

Output (line-wrapped for presentation):

Result is: {1 {Mount Karangetang} Indonesia 2001-01-25} {2 Hakkoda Japan
  1997-07-12} {3 Akutan Alaska 1996-03-10} {4 Momotombo Nicaragua 1996-04-04}

Example 5.10. Result data: -assignbyidx

# pg_result -assignbyidx is similar to -assign in that it assigns all the
# values from all the rows in the query to a single array, but it forms
# the array indexes using the value of the first column of the query result
# (typically the table primary key column), followed by the field names,
# followed by an optional suffix. Note that the first column of the result
# is not stored in the array - it is used as part of the index for the
# other values in each row.
# In this example, the optional suffix is not used.
pg_result $result -assignbyidx MyArray
parray MyArray


MyArray(1,last_eruption) = 2001-01-25
MyArray(1,location)      = Indonesia
MyArray(1,volcano)       = Mount Karangetang
MyArray(2,last_eruption) = 1997-07-12
MyArray(2,location)      = Japan
MyArray(2,volcano)       = Hakkoda
MyArray(3,last_eruption) = 1996-03-10
MyArray(3,location)      = Alaska
MyArray(3,volcano)       = Akutan
MyArray(4,last_eruption) = 1996-04-04
MyArray(4,location)      = Nicaragua
MyArray(4,volcano)       = Momotombo

Example 5.11. Result data: -dict

# pg_result -dict returns all the values from all the rows in the query
# result, similar to -assign, but it stores them in a Tcl dictionary rather
# than an array. This requires Tcl-8.5 or higher.
# The returned value is a two-level dictionary. The outer keys are row
# numbers starting from 0, and the inner keys are column names.
set MyDict [pg_result $result -dict]
dict for {i row} $MyDict {
  dict for {field value} $row {
    puts "MyDict.$i.$field = $value"
puts "\nLocation from row 2: [dict get $MyDict 2 location]"


MyDict.0.id = 1
MyDict.0.volcano = Mount Karangetang
MyDict.0.location = Indonesia
MyDict.0.last_eruption = 2001-01-25
MyDict.1.id = 2
MyDict.1.volcano = Hakkoda
MyDict.1.location = Japan
MyDict.1.last_eruption = 1997-07-12
MyDict.2.id = 3
MyDict.2.volcano = Akutan
MyDict.2.location = Alaska
MyDict.2.last_eruption = 1996-03-10
MyDict.3.id = 4
MyDict.3.volcano = Momotombo
MyDict.3.location = Nicaragua
MyDict.3.last_eruption = 1996-04-04

Location from row 2: Alaska


Some pg_result methods may not be usable with queries that produce results with duplicate column names.

Queries can produce results with duplicate column names due to table joins, column aliasing, or other SQL constructs. (Note that the column names as seen by Pgtcl do not include table name prefixes. If tables A and B both have a column named 'amount', then including A.amount and B.amount in a query will result in two columns named 'amount'.)

The pg_result options -tupleArray, -assign, -assignbyidx, and -dict all use the names of the query result columns as array indexes or dictionary keys. Since array indexes and dictionary keys are by definition unique, only one of the columns with the same name can be returned. (Exactly which of the duplicate column name values is returned is not defined.)

To avoid this, use column name aliases in your SQL query to make all column names unique, or use one of the other access methods that does not rely on column names: -getTuple, -list, or -llist.

SourceForge.net Logo

This version of the manual was produced for the Pgtcl-ng Sourceforge project web service site, which requires the logo on each page.

To download a logo-free copy of the manual, see the Pgtcl-ng project downloads area.