5.4.Example - 問合せ結果を得る別法 The Different Ways to Get Query Results
Prev | Chapter5.Example Programs | Next

## 5.4.Example - The Different Ways to Get Query Results

pgtcl は問合せ結果を得る為の多くの異なる方法を有す｡この用例の目的は､問合せ結果データが返される異なる方法の明確化である｡ 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.

Example5.5.Result data: -getTuple

    # pg_result -getTuple は､零から数えた 行番号引数 によって選択された､
    # 一個の行(組)からの結果の列値を含む tclリストを返す｡
    # 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

Example5.6.Result data: -tupleArray

    # pg_result -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

Example5.7.Result data: -assign

    # pg_result -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

Example5.8.Result data: -list

    # pg_result -list は問合せ結果全体を含むtclリストを返す｡
    # 結果の細目は､行-主､列-従 の順序｡
    # 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

Example5.9.Result data: -llist

    # pg_result -llist は､問合せ全体を含むリストのtclリストを返す｡
    # それぞれの外側のリストの項目は､問合せ結果の一個の行に拘わり､
    # 内側のリストはその行の列の値を含む｡
    # 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}

Example5.10.Result data: -assignbyidx

    # pg_result -assignbyidx は -assign に似ていて､問合せの中の全ての値からの全ての値を
    # 一個の配列に代入しているが､問合せの結果の最初の列の値(典型的には表の主鍵欄)を
    # 使って配列索引を形成し､それに項目名が続き､更に､追加の接尾辞が続く｡
    # 註意すべきは､結果の最初の列は配列に保存されない - それは､各行内の他の値の
    # 為の索引の部分として使われる｡
    # この用例では､追加の接尾辞は使われない｡
    # 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

Example5.11.Result data: -dict

    # pg_result -dict は､ -assign に似て､問合せの結果の中の全ての
    # 列から､全ての値を返す､但し､配列ではなく､tcl辞書内に保存する｡
    # これは､ Tcl-8.5 版以降を要す｡
    # 返される値は､２-水準辞書である｡外側の鍵は 0 から始まる行番号､
    # 内側鍵は列の名前である｡
    # 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]"

出力 Output:

    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

### 註意 Note

幾つかの pg_result 方法は､重複した列名を生出して､利用できないかもしれない｡ Some `pg_result` methods may not be usable with queries that produce results with duplicate column names.

問合せでは､表の結合や､列の再整列や､他のSQL構築により､重複した列の名前を生成し得る｡ (註意 Pgtcl で見られる様に､列の名前は表名の前置名を含まない｡ もし､表 A と B の両方に'amount'の名前の列があると､ 中に A.amount と B.amount を含む問合せは､ 'amount'と名付けられた二つの列がある結果になる｡) 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'.)

pg_result の指示項目の､ -tupleArrayと, -assignと, -assignbyidxと -dict の全ては､ 配列索引や鍵として､問合せ結果の列の名前を使う｡ 配列索引や鍵は唯一に定められるので､同じ名前の一個だけの列が返され得る｡ (正確には､重複した列名の値が返されるものは､定義されない｡) 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.)

それを避けるには､ 貴兄のSQL問合せ内に､列名の偽名を使って､全ての列名を重複無しにするか､ 列名に依存しない他の使用方法のどれかを使う:即ち -getTupleか､ -listか -llist を使う｡ 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`.

Prev | Up | Next
5.3.Example - Get List of Databases | Home | 5.5.Example - Large Objects - Picture Viewer, Part 1 - Store Pictures