Thread: pg_type questions
I'm working on psql printing routines here and want to do alignment by datatype. Two questions arose: 1) What is the difference between/merit of "line" vs. "_line", "cidr" vs. "_cidr", etc.? Do I have to worry about them? 2) Can I assume that the Oids for the datatypes are always the same (barring a developer changing them, of course)? Where are they defined? What would be the best way to digest the output of libpq's PQftype()? (Perhaps a char * PQftypetext() would be of general use?) (The issue here is that I do _not_ want to have to query pg_type for that information, since psql has no business contacting the database server when not asked to do so.) Thanks,Peter -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> I'm working on psql printing routines here and want to do alignment by > datatype. Two questions arose: > > 1) What is the difference between/merit of "line" vs. "_line", "cidr" vs. > "_cidr", etc.? Do I have to worry about them? _line is for line arrays. > > 2) Can I assume that the Oids for the datatypes are always the same > (barring a developer changing them, of course)? Where are they defined? > What would be the best way to digest the output of libpq's PQftype()? > (Perhaps a char * PQftypetext() would be of general use?) src/include/catalog. There is an unused_oid script in there too. -- 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, Pennsylvania19026
Peter Eisentraut <peter_e@gmx.net> writes:
> What would be the best way to digest the output of libpq's PQftype()?
> (Perhaps a char * PQftypetext() would be of general use?)
> (The issue here is that I do _not_ want to have to query pg_type for that
> information, since psql has no business contacting the database server
> when not asked to do so.)
You can't have it both ways: either you look up the OID in pg_type,
or the info you provide is incomplete/unreliable.
For the standard system types like int4, text, etc, it's probably OK
for client code to assume that particular numeric OIDs correspond to
those types --- use the #defines that are in catalog/pg_type.h, such as
BOOLOID, to refer to those types. (I think there are one or two places
in libpq and/or psql that do this already, eg, to decide whether a
column is "numeric".) The backend does this all over the place, but
it's a little shakier for frontend code to do it, because a frontend
might possibly be used with other database versions than the one it was
compiled for. Still, I think you could get away with it for standard
types --- AFAIK no one has any intention of renumbering those.
(Thomas has been muttering dire things about the date/time types, so
you might be well advised not to assume anything about those ;-).)
It would not be an unreasonable idea for libpq to provide a general
purpose type-OID-to-type-name mapper, with the understanding that this
mapper *would* query the backend at need. (Of course it should know
automatically about the most common standard types, and it should cache
the results of previous lookups so any one OID is queried at most once
per connection.) We have heard from a number of people who have built
exactly that facility for their applications, so it's obviously useful.
regards, tom lane
> Peter Eisentraut <peter_e@gmx.net> writes: > > What would be the best way to digest the output of libpq's PQftype()? > > (Perhaps a char * PQftypetext() would be of general use?) > > > (The issue here is that I do _not_ want to have to query pg_type for that > > information, since psql has no business contacting the database server > > when not asked to do so.) > > You can't have it both ways: either you look up the OID in pg_type, > or the info you provide is incomplete/unreliable. > > For the standard system types like int4, text, etc, it's probably OK > for client code to assume that particular numeric OIDs correspond to > those types --- use the #defines that are in catalog/pg_type.h, such as > BOOLOID, to refer to those types. (I think there are one or two places > in libpq and/or psql that do this already, eg, to decide whether a > column is "numeric".) The backend does this all over the place, but > it's a little shakier for frontend code to do it, because a frontend > might possibly be used with other database versions than the one it was > compiled for. Still, I think you could get away with it for standard > types --- AFAIK no one has any intention of renumbering those. > > (Thomas has been muttering dire things about the date/time types, so > you might be well advised not to assume anything about those ;-).) > > It would not be an unreasonable idea for libpq to provide a general > purpose type-OID-to-type-name mapper, with the understanding that this > mapper *would* query the backend at need. (Of course it should know > automatically about the most common standard types, and it should cache > the results of previous lookups so any one OID is queried at most once > per connection.) We have heard from a number of people who have built > exactly that facility for their applications, so it's obviously useful. Yes, Tom is exactly right on all these points. My suggestion to look in include/catalog was just my quick answer. -- 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, Pennsylvania19026
On Oct 17, Tom Lane mentioned: > For the standard system types like int4, text, etc, it's probably OK > for client code to assume that particular numeric OIDs correspond to > those types --- use the #defines that are in catalog/pg_type.h, such as > BOOLOID, to refer to those types. (I think there are one or two places > in libpq and/or psql that do this already, eg, to decide whether a > column is "numeric".) The backend does this all over the place, but > it's a little shakier for frontend code to do it, because a frontend > might possibly be used with other database versions than the one it was > compiled for. Still, I think you could get away with it for standard > types --- AFAIK no one has any intention of renumbering those. > > (Thomas has been muttering dire things about the date/time types, so > you might be well advised not to assume anything about those ;-).) The previous alignment "algorithm" in psql in essence checked for [^-+0-9\.eE] or sth like that as far as I could tell. Right now I am just aligning int[248], float[48], and numeric but the date/time types might be nice as well. But the idea was to straighten this out a bit so that alignment for other datatypes could easily be added or removed. I think I might have accomplished that ;) -Peter -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden