pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedType OIDs
6+ messages / 3 participants
[nested] [flat]
* Type OIDs
@ 2009-06-03 18:45 Florian Weimer <fw@deneb.enyo.de>
0 siblings, 1 reply; 6+ messages in thread
From: Florian Weimer @ 2009-06-03 18:45 UTC (permalink / raw)
To: pgsql-interfaces
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Type OIDs
@ 2009-06-03 19:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Florian Weimer <fw@deneb.enyo.de>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2009-06-03 19:26 UTC (permalink / raw)
To: Florian Weimer <fw@deneb.enyo.de>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Type OIDs
@ 2009-06-04 01:18 Joe Conway <mail@joeconway.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Joe Conway @ 2009-06-04 01:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Florian Weimer <fw@deneb.enyo.de>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Type OIDs
@ 2009-06-06 10:32 Florian Weimer <fw@deneb.enyo.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Florian Weimer @ 2009-06-06 10:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
* 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).
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Type OIDs
@ 2009-06-06 15:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Florian Weimer <fw@deneb.enyo.de>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2009-06-06 15:15 UTC (permalink / raw)
To: Florian Weimer <fw@deneb.enyo.de>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Type OIDs
@ 2009-06-07 10:22 Florian Weimer <fw@deneb.enyo.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Florian Weimer @ 2009-06-07 10:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
* 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-06-07 10:22 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-06-03 18:45 Type OIDs Florian Weimer <fw@deneb.enyo.de>
2009-06-03 19:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-06-04 01:18 ` Joe Conway <mail@joeconway.com>
2009-06-06 10:32 ` Florian Weimer <fw@deneb.enyo.de>
2009-06-06 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-06-07 10:22 ` Florian Weimer <fw@deneb.enyo.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox