Re: 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(at)deneb(dot)enyo(dot)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(at)deneb(dot)enyo(dot)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(at)deneb(dot)enyo(dot)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(at)deneb(dot)enyo(dot)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(at)deneb(dot)enyo(dot)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.