Hi,

In libpgtcl, pg_select an array field is return as the following string:

{"red","blue","green"}

and it's rather difficult to process them as a normal tcl list.
The same thing for pg_exec, pg_result/tupleArray

I think it would be better to return the string as:

"red" "blue" "green"

and tcl users could directly process the array as an ordinary tcl list.

As far as I know, arrays are not heavily used by libpgtcl users (thought
it's an interesting feature) so changing format would not affect too
many applications but will encourage array field usage in future.

What do you think?

Constantin Teodorescu
FLEX Consulting Braila, ROMANIA

Wed, 11 Aug 1999 06:26:23 +0000
Constantin Teodorescu <teo@flex.ro>
libpgtcl and array fields return format - PROPOSAL
> I think it would be better to return the string as:
> "red" "blue" "green"
> and tcl users could directly process the array as an ordinary tcl list.

Do it!

                        - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Wed, 11 Aug 1999 12:23:05 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] libpgtcl and array fields return format - PROPOSAL
> > > I think it would be better to return the string as:
> > > "red" "blue" "green"
> > > and tcl users could directly process the array as an ordinary tcl list.

Would it be also possible to use simple lists for arrays on *input* as
well as output? The implementation would be symmetric and (presumably)
easier to use...

                     - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Wed, 11 Aug 1999 12:32:26 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] libpgtcl and array fields return format - PROPOSAL
Thomas Lockhart wrote:
> 
> > I think it would be better to return the string as:
> > "red" "blue" "green"
> > and tcl users could directly process the array as an ordinary tcl list.
> 
> Do it!

All right!

Just waited for an official approval!

:-)

-- 
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA

Wed, 11 Aug 1999 12:32:40 +0000
Constantin Teodorescu <teo@flex.ro>
Re: [HACKERS] libpgtcl and array fields return format - PROPOSAL
Thomas Lockhart wrote:
> 
> > > > I think it would be better to return the string as:
> > > > "red" "blue" "green"
> > > > and tcl users could directly process the array as an ordinary tcl list.
> 
> Would it be also possible to use simple lists for arrays on *input* as
> well as output? The implementation would be symmetric and (presumably)
> easier to use...

I am note sure that I am understanding the problem.
There is no *input* format in Tcl.
The only way for adding data to a table is:
pg_exec $dbc "insert into ....." and that's the PostgreSQL syntax.
There's no "living" snapshots in Tcl as in JDBC 2 (updatable
recordsets).

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?

-- 
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA

Wed, 11 Aug 1999 13:06:29 +0000
Constantin Teodorescu <teo@flex.ro>
Re: [HACKERS] libpgtcl and array fields return format - PROPOSAL
> 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?

No, that is the issue I was bringing up. Perhaps we at least need
"convert to/from" functions to help with formatting arrays??

> 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?

So this is what you were proposing anyway, right? Or would you have
other changes to make too?

                      - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Wed, 11 Aug 1999 13:19:08 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] libpgtcl and array fields return format - PROPOSAL
> 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.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 11 Aug 1999 10:14:09 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] libpgtcl and array fields return format - PROPOSAL
> > 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  |
+----------------------------------------------------------------------+

Fri, 13 Aug 1999 13:30:17 +0200 (MEST)
Massimo Dal Zotto <dz@wizard.net>
Re: [HACKERS] libpgtcl and array fields return format - PROPOSAL