The result of a search is the number of matching rows.
set count [$table search -compare {{= fieldname value}}]
If the field is indexed, you can use index span:
set span [$table index span fieldname] set lowest [lindex $span 0] set highest [lindex $span 1]
otherwise, sort by the field, with a limit of 1.
# To find the lowest value, sort ascending.
$table search -fields fieldname -get row -sort fieldname -limit 1 -code {
set lowest [lindex $row 0]
}
# To find the highest value, sort descending
$table search -fields fieldname -get row -sort -fieldname -limit 1 -code {
set highest [lindex $row 0]
}
set meta_table [table type] set definition [$meta_table cextension]
proc field_max {table field} {
if {[lsearch [$table field indexed] $field] != -1} {
return [lindex [$table index span $field] 1]
} else {
set max ""
$table search -sort -$field -limit 1 -code { set max $field }
return $max
}
}
[anim_characters type] method max field_max
puts "Oldest character is [anim_characters max age]"
The -write_tabsep option to search lets you write all or part of a Speed Table to a file
tableType create t ... set fp [open t.out w] t search -write_tabsep $fp close $fp
This copies the entire table t to the file t.out. Note that you could as easily have specified an open socket or any other sort of Tcl channel that might exist in place of the file. You could restrict what gets copied using addition search options, for example:
set fp [open high-severity-report.tsv w]
t search \
-compare {{> severity 90}} \
-fields {name device severity} \
-write_tabsep $fp
close $fp
Given a token-separated file, you use the read_tabsep method.
set fp [open new_anim_characters.txt r] t read_tabsep $fp close $fp
When reading a large table, it may be faster to drop the indexes before reading the table, and recreate them afterwards:
t index drop name t index from show set fp [open really_big_file r] t read_tabsep $fp close $fp t index create name t index create show
Or, if you don't know all the indexes:
set indexed [t index indexed]
foreach f $indexed {t index drop $f}
set fp [open really_big_file r]
t read_tabsep $fp
close $fp
foreach f $indexed {t index create $f}
PostgreSQL supports copying from a file (or standard input) to a table:
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]
In the Tcl bindings for PostgreSQL, the database connection is actually a Tcl channel. This means you can use COPY table FROM STDIN and then use search -write_tabsep $connection to write the table to the database.
Here's an example of taking a speed table and copying it it to a PostgreSQL table.
package require Pgtcl
source cpescan.ct
package require Cpe_scan
# Note that the null_value is applied to the meta-table (the speed table
# class, so as to speak) and not the individual speed table itself.
cpe_scan null_value \\N
# read the table from a file
cpe_scan create cpe
set fp [open saved_cpe_scan.txt]
cpe read_tabsep $fp
close $fp
# Open the database connection
set db [pg_connect -conninfo $login_info]
#
# note double-backslashing on the null value and that we set the null value
# to match the null_value set with the speed table.
#
set res [
pg_exec $db "copy cpe_scan_info from stdin with delimiter as '\t' null as '\\\\N'"
]
#
# after you've started it, you expect the postgres response handle's status
# to be PGRES_COPY_IN
#
if {[pg_result $res -status] != "PGRES_COPY_IN"} {
puts "[pg_result $res -status] - bailing"
puts "[pg_result $res -error]"
exit
}
#
# next you use the write_tabsep search option to write to the database handle
#
cpe search -write_tabsep $db
#
# then send a special EOF sequence.
#
puts $db "\\."
#
# the result handle previously returned will now have magically changed
# its status to the normal PGRES_COMMAND_OK response.
#
puts [pg_result $res -status]
# And clean up
pg_result $res -clear
NOTE that all the records must be accepted by PostgreSQL, i.e. not violate any constraints, etc, or none of them will be.