Thread: 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 typtype = 'b' AND typarray <> 0 AND typname::regtype = 'integer';

Many thanks to Florian Pflug.




--
// 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.

Actually, all I need is to:

SELECT oid::regtype, oid FROM pg_type WHERE ...

to make cache of OIDs.




--
// 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.