Обсуждение: OID of type by name.
Hey general@,
SELECT oid FROM pg_type WHERE typname = 'integer';
oid
-----
(0 rows)
SELECT oid FROM pg_type WHERE typname = 'int4';
oid
-----
23
(1 row)
How can I get OID by name rather than alias ?
--
// Dmitriy.
SELECT oid FROM pg_type WHERE typname = 'integer';
oid
-----
(0 rows)
SELECT oid FROM pg_type WHERE typname = 'int4';
oid
-----
23
(1 row)
How can I get OID by name rather than alias ?
--
// Dmitriy.
SELECT oid FROM pg_type WHERE typtype = 'b' AND typarray <> 0 AND typname::regtype = 'integer';
Many thanks to Florian Pflug.
--
// Dmitriy.
Many thanks to Florian Pflug.
2010/12/8 Dmitriy Igrishin <dmitigr@gmail.com>
Hey general@,
SELECT oid FROM pg_type WHERE typname = 'integer';
oid
-----
(0 rows)
SELECT oid FROM pg_type WHERE typname = 'int4';
oid
-----
23
(1 row)
How can I get OID by name rather than alias ?
--
// Dmitriy.
--
// Dmitriy.
Dmitriy Igrishin <dmitigr@gmail.com> writes:
>> How can I get OID by name rather than alias ?
> SELECT oid FROM pg_type WHERE typtype = 'b' AND typarray <> 0 AND
> typname::regtype = 'integer';
Seems like the hard way --- if you think carefully about what regtype
is doing, you'll realize that this is incredibly inefficient, as well
as a lot of typing. I usually do SELECT 'integer'::regtype::oid
when I need a quick numeric lookup.
regards, tom lane
Yeah, thank you very much!
I've found it already too, but not post back!
Thanks!
--
// Dmitriy.
I've found it already too, but not post back!
Thanks!
2010/12/8 Tom Lane <tgl@sss.pgh.pa.us>
> SELECT oid FROM pg_type WHERE typtype = 'b' AND typarray <> 0 ANDSeems like the hard way --- if you think carefully about what regtype
> typname::regtype = 'integer';
is doing, you'll realize that this is incredibly inefficient, as well
as a lot of typing. I usually do SELECT 'integer'::regtype::oid
when I need a quick numeric lookup.
regards, tom lane
--
// Dmitriy.
Actually, all I need is to:
SELECT oid::regtype, oid FROM pg_type WHERE ...
to make cache of OIDs.
--
// Dmitriy.
SELECT oid::regtype, oid FROM pg_type WHERE ...
to make cache of OIDs.
2010/12/8 Dmitriy Igrishin <dmitigr@gmail.com>
Yeah, thank you very much!
I've found it already too, but not post back!
Thanks!2010/12/8 Tom Lane <tgl@sss.pgh.pa.us>> SELECT oid FROM pg_type WHERE typtype = 'b' AND typarray <> 0 ANDSeems like the hard way --- if you think carefully about what regtype
> typname::regtype = 'integer';
is doing, you'll realize that this is incredibly inefficient, as well
as a lot of typing. I usually do SELECT 'integer'::regtype::oid
when I need a quick numeric lookup.
regards, tom lane
--
// Dmitriy.
--
// Dmitriy.
In what version of PG is the 'my_type'::regtype::oid syntax available? I want to introduce this to the JDBC driver which currently does the most ridiculous query that totally ignores search path. JDBC driver does this currently: SELECT oid FROM pg_catalog.pg_type WHERE typname = ? So if you have more than one type of the same name (or perhaps a UDT and a table with the same name as in my case) it just grabs whichever one first appears in pg_type regardless of search path. So I intend to change that query to: SELECT ?::regtype::oid But I need to know if I should be checking the server version or not. What is the min version that that query will work on? -- View this message in context: http://postgresql.1045698.n5.nabble.com/OID-of-type-by-name-tp3297240p5750103.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
2013/3/29 tahoe-gary <gbaker@salesforce.com>
-- In what version of PG is the 'my_type'::regtype::oid syntax available? I
want to introduce this to the JDBC driver which currently does the most
ridiculous query that totally ignores search path.
JDBC driver does this currently: SELECT oid FROM pg_catalog.pg_type WHERE
typname = ?
So if you have more than one type of the same name (or perhaps a UDT and a
table with the same name as in my case) it just grabs whichever one first
appears in pg_type regardless of search path.
So I intend to change that query to: SELECT ?::regtype::oid
But I need to know if I should be checking the server version or not. What
is the min version that that query will work on?
According to the documentation, I think that Object Identifier Types was introduced in 7.3.
// Dmitriy.