Re: [HACKERS] libpgtcl and array fields return format - PROPOSAL - Mailing list pgsql-hackers

> > For the moment, the current syntax helps PgAccess. It returns exactly
> > the same format as it would be used to INSERT INTO queries so if you
> > would try to define a table with an array field of strings for example
> > you are able to add records and update them directly from PgAccess.
> > >From that point of view, the new array field return format would give me
> > headaches for Pgaccess in order to restore the {"..","..",".."} format
> > used for updating records.
> > 
> > Am I missing something about the *input* format?
> > 
> > On the other hand, I have discovered in the libpgtcl source that there
> > is a TCL_ARRAYS that if defined, would return array fields format
> > exactly as a tcl list. But it is not defined anywhere. I think that the
> > behaviour of libpgtcl should be consistent so should we define
> > TCL_ARRAYS by default in the next release?
> 
> That define is from Massimo.  Let's enable it.
> 

That define is not enabled by default because it requires also a change in
the array output format in order to distinguish between the scalar and array
values returned by the backend. It requires also my string-io contrib module
if I remember correctly.

I'm obviously using the TCL_ARRAYS feature from years and it works fine for
me, but it could break other non tcl frontends which don't expect the new
string quoting format, so I won't advise to enable it by default.

This is a very ancient problem of postgres which has never been resolved.
I proposed a solution using a C-like syntax for strings and arrays but it
wasn't accepted. I think we should discuss again the string and array
formats used by pgsql and find a common and non-ambiguous format for all
the i/o routines.

Regarding the input I chose to implement a tcl layer which accepts tcl
values, converts them to sql values and formats them into predefined query.
For example:
  defineQuery update_foo \"update foo set arr_val = %s, date_val = %s where key = %s" \{{string 1} date string}
  defineQuery select_foo \"select * from foo where str_val = %s and date_val = %d" \{string date}
  execQuery update_foo {"a1 a2 a3" "31/12/1999" "x y z"} -cmd  set rows [execQuery select_foo {"x y z" "31/12/1999"}
-rows]

The execQuery formats the tcl values accordingly to the types defined in
the defineQuery, submits the sql statement and converts back the result
to the required format (rows or TclX keyed-lists).
Besides this the execQuery can also keep a cache of previous query results
and avoid resubmitting the query if defined as cacheable.

Unfortunately my tcl layer is really a mess and depends on some my other
tcl code, so I have never submitted it as contributed code.

I think it would be difficult to put the input conversion code into the
libpgtcl library because you don't have any indication of the sql format
required by the various values supplied in the query.

-- 
Massimo Dal Zotto

+----------------------------------------------------------------------+
|  Massimo Dal Zotto               email: dz@cs.unitn.it               |
|  Via Marconi, 141                phone: ++39-0461534251              |
|  38057 Pergine Valsugana (TN)      www: http://www.cs.unitn.it/~dz/  |
|  Italy                             pgp: finger dz@tango.cs.unitn.it  |
+----------------------------------------------------------------------+