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) |
---|---|---|---|
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 |
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]
Output:
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
Output:
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
Output:
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
Output:
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