Re: [Sqlrelay-discussion] getColumnType returns 'UNKNOWN' for PostgreSQL ARRAY column type.
Brought to you by:
mused
Menu
▾
▴
Re: [Sqlrelay-discussion] getColumnType returns 'UNKNOWN' for PostgreSQL ARRAY column type.
|
From: David M. <dav...@fi...> - 2007-09-18 02:43:32
|
Aaah, somehow I managed to reply to myself a while back. I just updated
the code. If you grab sqlrelay from CVS, it should have all of this
taken care of.
Below is the original message that I intended to send to you:
Dave
dav...@fi...
Ok, here's how it works:
postgresql types are just numbers called oid's. They correspond to
entries in a table called pg_type.
In the sqlrelay.conf file's postgresql connect string, you can set the
typemangling parameter to "no", "yes" or "lookup". Setting it to "no"
will return the oid, "yes" will do a static translation and "lookup"
will cause sqlrelay to "select oid,typname from pg_type" at startup,
cache the result and do a translation based on that.
There is actually a bug though. Setting typemangling="no" currently has
the same effect as "lookup". You have to remove the typemangling
parameter altogether to get the "no" behavior. This will be fixed in
the next major release.
At any rate. If I set typemangling="lookup" then I get "_text" for the
column type. Removing the typemangling parameter gave me 1009 for the
column type. Similar tests with other array types returned "_int8",
"_int2", etc. It appears that if there's an underscore in the column
type, then it's an array type.
So, the real issue is that when typemangling="yes", it doesn't correctly
translate the type. This can be fixed by editing
src/connections/postgresql/postgresqlconnection.C, around line 556 or
so. There's a big if block (which really could be a switch block) which
tests pgfieldtype (the result of PQftype) against a set of common
datatypes. But it's an incomplete list. It doesn't even handle all of
the different types defined in common/datatypes.h for postgresql. You
could add support for array types (and the other missing types) by
extending that if block. You can get the oid and type names for
"built-in" data types by running a "select oid,typname from pg_type
where oid < 10000" query. You might also want to update
common/datatypes.h and change the type names from names like
"_TEXT_DATATYPE" to names like "TEXT_ARRAY_DATATYPE" or similar.
Give it a try, I'd be glad to accept a patch like this.
Dave
dav...@fi...
On Fri, 2007-09-14 at 17:40 -0400, Alfred J Fazio wrote:
> Should I assume that this is not possible? Thank you for your time.
>
> On 9/7/07, Alfred J Fazio <alf...@gm...> wrote:
> Many thanks, David. If you find that you do not have the time
> to implement this, feel free to point me in the right
> direction and I'll pick up from there. I wouldn't mind adding
> clauses for additional column types such as bit strings, etc.
> Again, thank you so much!
>
> --
> Alfred J Fazio,
> alf...@gm...
>
> On 9/7/07, David Muse <dav...@fi...> wrote:
> This should be simple to fix. I'm looking into it now
> and I'll let you
> know what I find shortly.
>
> Dave
> dav...@fi...
>
> On Wed, 2007-09-05 at 16:29 -0400, Alfred J Fazio
> wrote:
> > Hi everyone,
> >
> > I am using SQLRelay 0.38 via Python (2.4.4) to
> connect to a
> > PostgreSQL (8.1.9) database. I have noticed that if
> I create a table
> > with an ARRAY column, that Sqlrelay returns the
> column type as
> > UNKNOWN. E.g., if I issue the following SQL:
> >
> > CREATE TABLE test (arr TEXT[][]);
> >
> > And fill it will some test data:
> >
> > INSERT INTO test (arr) VALUES (ARRAY[['fname',
> 'alfred'], ['lname',
> > 'fazio'], ['age', '24'], ['employer',
> 'smoothstone']]);
> >
> > I will get an UNKNOWN value when calling
> getColumnType on the arr
> > column above:
> >
> > >>> c.sendQuery("SELECT arr FROM test")
> > >>> c.getField(0, 0)
> >
> '{{fname,alfred},{lname,fazio},{age,24},{employer,smoothstone}}'
> > >>> c.getColumnType(0)
> > 'UNKNOWN'
> >
> > It would be very convenient if I could test whether
> the column I am
> > fetching is an ARRAY. If so, I can automatically
> parse the result of
> > the query into a native Python list. I briefly
> peeked in the source
> > code and saw that there has been a datatype declared
> for arrays for
> > PostgreSQL in the src/common/datatypes.h file
> (called 'ANYARRAY').
> > However, I didn't look around for long to see where
> I might implement
> > the code needed to detect the column type. Could
> somebody please
> > point me to the right direction within the source
> code where I can
> > perhaps add a clause for this situation? I will
> then be happy to
> > submit a patch to the list.
> >
> > Thanks for your time!
> >
> > --
> > Alfred J Fazio,
> > alf...@gm...
> >
> -------------------------------------------------------------------------
> > This SF.net email is sponsored by: Splunk Inc.
> > Still grepping through log files to find
> problems? Stop.
> > Now Search log events and configuration files using
> AJAX and a browser.
> > Download your FREE copy of Splunk now >>
> http://get.splunk.com/
> > _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find
> problems? Stop.
> Now Search log events and configuration files using
> AJAX and a browser.
> Download your FREE copy of Splunk now >>
> http://get.splunk.com/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
>
>
> --
> Alfred J Fazio,
> alf...@gm...
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________ Sqlrelay-discussion mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
|
