Home Explore Blog CI



postgresql

59th chunk of `doc/src/sgml/datatype.sgml`
a81eb1e7371e961fe5a7fcc8272950d28bed6cd1199635ee0000000100000fa1
 <type>oid</type> would use.  The alias
    types allow simplified lookup of OID values for objects.  For example,
    to examine the <structname>pg_attribute</structname> rows related to a table
    <literal>mytable</literal>, one could write:
<programlisting>
SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;
</programlisting>
    rather than:
<programlisting>
SELECT * FROM pg_attribute
  WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');
</programlisting>
    While that doesn't look all that bad by itself, it's still oversimplified.
    A far more complicated sub-select would be needed to
    select the right OID if there are multiple tables named
    <literal>mytable</literal> in different schemas.
    The <type>regclass</type> input converter handles the table lookup according
    to the schema path setting, and so it does the <quote>right thing</quote>
    automatically.  Similarly, casting a table's OID to
    <type>regclass</type> is handy for symbolic display of a numeric OID.
   </para>

    <table id="datatype-oid-table">
     <title>Object Identifier Types</title>
     <tgroup cols="4">
      <thead>
       <row>
        <entry>Name</entry>
        <entry>References</entry>
        <entry>Description</entry>
        <entry>Value Example</entry>
       </row>
      </thead>

      <tbody>

       <row>
        <entry><type>oid</type></entry>
        <entry>any</entry>
        <entry>numeric object identifier</entry>
        <entry><literal>564182</literal></entry>
       </row>

       <row>
        <entry><type>regclass</type></entry>
        <entry><structname>pg_class</structname></entry>
        <entry>relation name</entry>
        <entry><literal>pg_type</literal></entry>
       </row>

       <row>
        <entry><type>regcollation</type></entry>
        <entry><structname>pg_collation</structname></entry>
        <entry>collation name</entry>
        <entry><literal>"POSIX"</literal></entry>
       </row>

       <row>
        <entry><type>regconfig</type></entry>
        <entry><structname>pg_ts_config</structname></entry>
        <entry>text search configuration</entry>
        <entry><literal>english</literal></entry>
       </row>

       <row>
        <entry><type>regdictionary</type></entry>
        <entry><structname>pg_ts_dict</structname></entry>
        <entry>text search dictionary</entry>
        <entry><literal>simple</literal></entry>
       </row>

       <row>
        <entry><type>regnamespace</type></entry>
        <entry><structname>pg_namespace</structname></entry>
        <entry>namespace name</entry>
        <entry><literal>pg_catalog</literal></entry>
       </row>

       <row>
        <entry><type>regoper</type></entry>
        <entry><structname>pg_operator</structname></entry>
        <entry>operator name</entry>
        <entry><literal>+</literal></entry>
       </row>

       <row>
        <entry><type>regoperator</type></entry>
        <entry><structname>pg_operator</structname></entry>
        <entry>operator with argument types</entry>
        <entry><literal>*(integer,&zwsp;integer)</literal>
         or <literal>-(NONE,&zwsp;integer)</literal></entry>
       </row>

       <row>
        <entry><type>regproc</type></entry>
        <entry><structname>pg_proc</structname></entry>
        <entry>function name</entry>
        <entry><literal>sum</literal></entry>
       </row>

       <row>
        <entry><type>regprocedure</type></entry>
        <entry><structname>pg_proc</structname></entry>
        <entry>function with argument types</entry>
        <entry><literal>sum(int4)</literal></entry>
       </row>

       <row>
        <entry><type>regrole</type></entry>
        <entry><structname>pg_authid</structname></entry>
        <entry>role name</entry>
        <entry><literal>smithee</literal></entry>
       </row>

       <row>
        <entry><type>regtype</type></entry>
        <entry><structname>pg_type</structname></entry>
        <entry>data

Title: Object Identifier Types in PostgreSQL
Summary
PostgreSQL provides various object identifier types, including oid, regclass, regproc, and regtype, which are used to reference system objects such as tables, functions, and roles. These types allow for simplified lookup and symbolic display of OID values, and are referenced in system catalogs such as pg_class and pg_proc. A table lists the different object identifier types, their references, descriptions, and example values.