Version 6 of getting data from pgtcl

Updated 2012-10-10 23:08:48 by RLE

The pgtcl library has many ways of providing data. Here are some of them.


Examples of using the numerous options for pg_result (pg::result):

 % set reshandle [pg_exec $connhandle "SELECT * FROM Pg_class LIMIT 5"]

Checking for query status:

 % pg_result $reshandle -status
 PGRES_TUPLES_OK

Checking for query error:

 % pg_result $reshandle -error

Getting the connection name:

 % pg_result $reshandle -conn
 pgsql188

Getting the number of tuples in the result set:

 % pg_result $reshandle -numTuples
 5

Getting the number of attributes in the result set:

 % pg_result $reshandle -numAttrs
 24

-assign arrayName assigns the data to an array, which is indexed by the tuple number, and the column name, i.e. arrayName(0,column_name)

 % pg_result $reshandle -assign resArray
 % parray resArray
 resArray(0,relacl)         = {postgres=a*r*w*d*R*x*t*/postgres,=r/postgres}
 resArray(0,relam)          = 0
 resArray(0,relchecks)      = 0
 resArray(0,relfilenode)    = 17100
     .
     .
     .
 resArray(4,reltoastidxid)  = 0
 resArray(4,reltoastrelid)  = 0
 resArray(4,reltriggers)    = 0
 resArray(4,reltuples)      = 0
 resArray(4,reltype)        = 17116
 resArray(4,relukeys)       = 0
 % unset resArray

-assignbyidx arrayName assigns the data to an array, which is indexed by the name of the first column, and the column name, i.e. arrayName(first_column,column_name).

 % pg_result $reshandle -assignbyidx resArray
 % parray resArray
 resArray(table_constraints,relacl)                = {postgres=a*r*w*d*R*x*t*/postgres,=r/postgres}
 resArray(table_constraints,relam)                 = 0
 resArray(table_constraints,relchecks)             = 0
 resArray(table_constraints,relfilenode)           = 17100
      .
      .
      .
 resArray(triggers,relrefs)                        = 0
 resArray(triggers,reltoastidxid)                  = 0
 resArray(triggers,reltoastrelid)                  = 0
 resArray(triggers,reltriggers)                    = 0
 resArray(triggers,reltuples)                      = 0
 resArray(triggers,reltype)                        = 17116
 resArray(triggers,relukeys)                       = 0

 % pg_result $reshandle -getTuple 0
 table_constraints 16977 17101 1 0 17100 0 0 0 0 f f v 9 0 0 0 0 0 f f t f    
 {{postgres=a*r*w*d*R*x*t*/postgres,=r/postgres}}
 % unset resArray

-tupleArray rowNumber arrayName assigns the data for one tuple (row), specified by rowNumber, to an array, which is indexed by the name of the column, i.e. arrayName(column_name).

 % pg_result $reshandle -tupleArray 0 resArray
 % parray resArray
 resArray(relacl)         = {postgres=a*r*w*d*R*x*t*/postgres,=r/postgres}
 resArray(relam)          = 0
 resArray(relchecks)      = 0
 resArray(relfilenode)    = 17100
 resArray(relfkeys)       = 0
 resArray(relhasindex)    = f
 resArray(relhasoids)     = f
 resArray(relhaspkey)     = f
      .
      .
      .
 resArray(reltoastidxid)  = 0
 resArray(reltoastrelid)  = 0
 resArray(reltriggers)    = 0
 resArray(reltuples)      = 0
 resArray(reltype)        = 17101
 resArray(relukeys)       = 0
 % pg_result $reshandle -attributes
 relname relnamespace reltype relowner relam relfilenode relpages reltuples reltoastrelid reltoastidxid relhasindex  
 relisshared relkind relnatts relchecks reltriggers relukeys relfkeys relrefs relhasoids relhaspkey relhasrules 
 relhassubclass relacl
 % pg_result $reshandle -lAttributes
 {relname 19 64} {relnamespace 26 4} {reltype 26 4} {relowner 23 4} {relam 26 4} {relfilenode 26 4} {relpages 23 4}  
 {reltuples 700 4} {reltoastrelid 26 4} {reltoastidxid 26 4} {relhasindex 16 1} {relisshared 16 1} {relkind 18 1} 
 {relnatts 21 2} {relchecks 21 2} {reltriggers 21 2} {relukeys 21 2} {relfkeys 21 2} {relrefs 21 2} {relhasoids 16 1} 
 {relhaspkey 16 1} {relhasrules 16 1} {relhassubclass 16 1} {relacl 1034 -1}

-list returns the data as one list. each column of each row are elements in the list

 % set mylist [pg_result $reshandle -list]
 table_constraints 16977 17101 1 0 17100 0 0 0 0 f f v 9 0 0 0 0 0 f f t f  
 {{postgres=a*r*w*d*R*x*t*/postgres,=r/postgres}} table_privileges 16977 17105 1 0 17104 0 0 0 0 f f v 8 0 0 0 0 0 f f 
 t f {{postgres=a*r*w*d*R*x*t*/postgres,=r/postgres}} tables 16977 17109 1 0 17108 0 0 0 0 f f v 9 0 0 0 0 0 f f t f 
 {{postgres=a*r*w*d*R*x*t*/postgres,=r/postgres}} ...

-llist option returns the data as a list of lists, where each element of the list represents each row in the results, and each sublist represents the columns for those rows.

 % set mylist [pg_result $reshandle -llist]
 {table_constraints 16977 17101 1 0 17100 0 0 0 0 f f v 9 0 0 0 0 0 f f t f  
 {{postgres=a*r*w*d*R*x*t*/postgres,=r/postgres}}} {table_privileges 16977 17105 1 0 17104 0 0 0 0 f f v 8 0 0 0 0 0 f  
 f t f ...

-dict option returns the data in dictionary format, so that you can use the data as a dictionary. Note that you need dictionary support in Tcl (either Tcl 8.5 or the dict extension) in order to use this option

 % set mydict [pg_result $reshandle -dict]
 3 {relpages 1 relfkeys 0 relnatts 2 relhasoids f relhassubclass f relam 403 reltoastidxid 0 reltuples 0  relacl {}
   relhaspkey f reltriggers 0 relname pg_toast_17173_index relrefs 0 reltype 0 relukeys 0 relchecks 0 relisshared f
 reltablespace 0 reltoastrelid 0 relnamespace 99 relowner 1 relfilenode 17177 relkind i relhasrules f relhasindex f}
      .
      .
      .
 % dict get $mydict 1
 relpages 0 relfkeys 0 relnatts 5 relhasoids f relhassubclass f relam 0 reltoastidxid 0 reltuples 0 relacl ...
 % % dict get $mydict 1 relname
 data_type_privileges