Thread: Type OIDs
I'd like to use type information in a query result to convert its elements to more appropriate types if necessary. Can I embed the types I'm interested in into my program, or should I query the server at least once per connection to get the mapping? I'm interested in types such as INTEGER, TEXT, BYTEA, etc. No user defined types, and nothing fancy. Ultra-long-term portability isn't required, either.
Florian Weimer <fw@deneb.enyo.de> writes:
> Can I embed the types I'm interested in into my program, or should I
> query the server at least once per connection to get the mapping? I'm
> interested in types such as INTEGER, TEXT, BYTEA, etc. No user
> defined types, and nothing fancy. Ultra-long-term portability isn't
> required, either.
Well, the type OIDs of the standard built-in types haven't changed in
at least ten years, but that doesn't mean we won't change 'em in the
future. I'd advise at least using the #define's from pg_type.h rather
than writing actual numeric constants.
regards, tom lane
Tom Lane wrote: > Florian Weimer <fw@deneb.enyo.de> writes: >> Can I embed the types I'm interested in into my program, or should I >> query the server at least once per connection to get the mapping? I'm >> interested in types such as INTEGER, TEXT, BYTEA, etc. No user >> defined types, and nothing fancy. Ultra-long-term portability isn't >> required, either. > > Well, the type OIDs of the standard built-in types haven't changed in > at least ten years, but that doesn't mean we won't change 'em in the > future. I'd advise at least using the #define's from pg_type.h rather > than writing actual numeric constants. I'll second that. It's exactly what I've been doing in PL/R for several years now. Joe
* Tom Lane: > Florian Weimer <fw@deneb.enyo.de> writes: >> Can I embed the types I'm interested in into my program, or should I >> query the server at least once per connection to get the mapping? I'm >> interested in types such as INTEGER, TEXT, BYTEA, etc. No user >> defined types, and nothing fancy. Ultra-long-term portability isn't >> required, either. > > Well, the type OIDs of the standard built-in types haven't changed in > at least ten years, but that doesn't mean we won't change 'em in the > future. I'd advise at least using the #define's from pg_type.h rather > than writing actual numeric constants. Okay, will do that. Thanks. By the way, the binary encoding would be pretty useful for BYTEA columns and parameters, but it's a pretty hefty burden for almost anything else. Wouldn't it make sense to add a format flag which basically says "binary if it's BYTEA, otherwise text"? I don't think many host languages make a strong distinction between BYTEA and TEXT types (except those which use UTF-16 and expose that to the user, like Java and C#). As a result, it is difficult to specify the right types when talking to the server. You don't want to label a column as BYTEA overeagerly because it will break type inference on the SQL side (I think).
Florian Weimer <fw@deneb.enyo.de> writes:
> By the way, the binary encoding would be pretty useful for BYTEA
> columns and parameters, but it's a pretty hefty burden for almost
> anything else. Wouldn't it make sense to add a format flag which
> basically says "binary if it's BYTEA, otherwise text"?
What is "easy" is very much in the eye of the beholder --- I would think
for instance that a lot of people would consider integer columns to be
easy enough to deal with in binary format. ntohl() isn't much of a
burden.
As far as output goes, I seem to recall some discussion awhile back of a
format value that would mean "send <some list of types> in binary" where
the specific list could be set by the client. This would seem to me to
be a lot more useful and less klugy than hard-wiring bytea as a special
case. On the input side it's much more questionable since (as you
noted) clients don't always have a solid grasp on which parameters
are which types.
regards, tom lane
* Tom Lane: > Florian Weimer <fw@deneb.enyo.de> writes: >> By the way, the binary encoding would be pretty useful for BYTEA >> columns and parameters, but it's a pretty hefty burden for almost >> anything else. Wouldn't it make sense to add a format flag which >> basically says "binary if it's BYTEA, otherwise text"? > > What is "easy" is very much in the eye of the beholder --- I would think > for instance that a lot of people would consider integer columns to be > easy enough to deal with in binary format. ntohl() isn't much of a > burden. The documentation is silent on alignment, so I would have thought that a memcpy() is needed, too. > As far as output goes, I seem to recall some discussion awhile back of a > format value that would mean "send <some list of types> in binary" where > the specific list could be set by the client. This would seem to me to > be a lot more useful and less klugy than hard-wiring bytea as a special > case. Yes, but it would be more difficult to implement, wouldn't it? (Of course, it's better to implement the full-blown version from the beginning if it is implemented ever.) > On the input side it's much more questionable since (as you noted) > clients don't always have a solid grasp on which parameters are > which types. The input side is actually *much* *more* problematic because right now, I've got this string, and I pass it to PostgreSQL, and depending on the query, I've got to BYTEA-encode it or not. There is no way to figure out if this is necessary for a particular parameter. If I specify a BYTEA type for all string columns, I break type enference (there's no conversion or cast for BYTEA to INTEGER, for instance). As a result, if you use BYTEA columns from one of the scripting languages, you end up with manually specificing BYTEA types. I hate that, and people forget it and complain when things break. In contrast, for the output side, I can look at the column type and decode the value if it's BYTEA. It's just an efficiency issue. The API itself isn't problematic.