Re: [GENERAL] Function for retreiving datatype

[Pierre-Frédéric Caillaud]

Example :
psql
create table test (id serial primary key, data10 varchar(10), data20  
varchar(20), data text );
insert into test (data10, data20, data) values ('ten','twenty','all i  
want');

python
import psycopg
db = psycopg.connect("host=localhost dbname=.....")
c = db.cursor()
c.execute( "SELECT * FROM test LIMIT 1;" )
print c.description
(('id', 23, None, 4, None, None, None), ('data10', 1043, None, 10, None,  
None, None), ('data20', 1043, None, 20, None, None, None), ('data', 25,  
None, -1, None, None, None))

Here the integer behind the name is the type-id, the next one which is not  
None is the length.
Lets paste the typids in postgres :

=> select typname,typelem from pg_type where typelem in (23,25,1043);
 typname  | typelem
----------+---------
 _int4    |      23
 _text    |      25
 _varchar |    1043
Using this you can easily print the types returned by whatever :
python
c.execute('rollback')
c.execute( "SELECT typelem,typname FROM pg_type WHERE typelem != 0" )
typmap = dict(c.fetchall())
c.execute( "SELECT * FROM test LIMIT 1;" )
print "\n".join(["%s\t: %s\t%d" % (field_name, typmap[typid], typlen) for  
field_name,typid,_,typlen,_,_,_ in c.description])
id      : _int4 4
data10  : _varchar      10
data20  : _varchar      20
data    : _text -1

c.dictfetchall()
[{'data20': 'twenty', 'data': 'all i want', 'id': 1, 'data10': 'ten'}]
Don't ask me what the remaining things returned in c.description are, I  
don't know. Read the docs.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match