Re: BUG #16991: regclass is not case sensitive causing "relation does not exist" error - Mailing list pgsql-docs

[ redirecting to pgsql-docs list ]

I wrote:
> Federico <cfederico87@gmail.com> writes:
>> I did not come across that documented behavior while searching for regclass
>> in the pg docs. The most relevant page I was able to find was the Object
>> Identifier Types page that does not mention it.
>> I'll look into proposing a change in that docs page to mention it, if
>> that's the appropriate location for it.

> Hmm ... I *thought* it was documented, but perhaps not.  If not,
> it's likely that the other reg* types are likewise underdocumented.

So what I was remembering was some text in the section about sequence
functions.  That probably seemed appropriate when they were the only
real use of regclass; but these days we have regclass-accepting functions
all over, not to mention other OID alias types.  I propose the attached
patch to move this info into the "Object Identifier Types" section.

            regards, tom lane

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 7c341c8e3f..43f99335dc 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4780,10 +4780,14 @@ SELECT * FROM pg_attribute
     </table>

    <para>
-    All of the OID alias types for objects grouped by namespace accept
-    schema-qualified names, and will
+    All of the OID alias types for objects that are grouped by namespace
+    accept schema-qualified names, and will
     display schema-qualified names on output if the object would not
     be found in the current search path without being qualified.
+    For example, <literal>myschema.mytable</literal> is acceptable input
+    for <type>regclass</type> (if there is such a table).  That value
+    might be output as <literal>myschema.mytable</literal>, or
+    just <literal>mytable</literal>, depending on the current search path.
     The <type>regproc</type> and <type>regoper</type> alias types will only
     accept input names that are unique (not overloaded), so they are
     of limited use; for most uses <type>regprocedure</type> or
@@ -4792,6 +4796,86 @@ SELECT * FROM pg_attribute
     operand.
    </para>

+   <para>
+    The input functions for these types also ignore whitespace, and will
+    fold upper-case letters to lower case, except within double quotes;
+    this is done to make the syntax rules similar to the way object names
+    are written in SQL.  Conversely, the output functions will use double
+    quotes if needed to make the output be a valid SQL identifier.  For
+    example, the OID of a function named <literal>Foo</literal> (with
+    upper case <literal>F</literal>) taking two integer arguments could be
+    entered as <literal>' "Foo" ( int, integer ) '::regprocedure</literal>.
+    The output would look like <literal>"Foo"(integer,integer)</literal>.
+    Both the function name and the argument type names could be
+    schema-qualified, too.
+   </para>
+
+   <para>
+    Many built-in <productname>PostgreSQL</productname> functions accept
+    the OID of a table, or another kind of database object, and for
+    convenience are declared as taking <type>regclass</type> (or the
+    appropriate OID alias type).  This means you do not have to look up
+    the object's OID by hand, but can just enter its name as a string
+    literal.  For example, the <function>nextval()</function> function
+    takes a sequence relation's OID, so you could call it like this:
+<programlisting>
+nextval('foo')              <lineannotation>operates on sequence <literal>foo</literal></lineannotation>
+nextval('FOO')              <lineannotation>same as above</lineannotation>
+nextval('"Foo"')            <lineannotation>operates on sequence <literal>Foo</literal></lineannotation>
+nextval('myschema.foo')     <lineannotation>operates on <literal>myschema.foo</literal></lineannotation>
+nextval('"myschema".foo')   <lineannotation>same as above</lineannotation>
+nextval('foo')              <lineannotation>searches search path for <literal>foo</literal></lineannotation>
+</programlisting>
+   </para>
+
+   <note>
+    <para>
+     When you write the argument of such a function as an unadorned
+     literal string, it becomes a constant of type <type>regclass</type>
+     (or the appropriate type).
+     Since this is really just an OID, it will track the originally
+     identified object despite later renaming, schema reassignment,
+     etc.  This <quote>early binding</quote> behavior is usually desirable for
+     object references in column defaults and views.  But sometimes you might
+     want <quote>late binding</quote> where the object reference is resolved
+     at run time.  To get late-binding behavior, force the constant to be
+     stored as a <type>text</type> constant instead of <type>regclass</type>:
+<programlisting>
+nextval('foo'::text)      <lineannotation><literal>foo</literal> is looked up at runtime</lineannotation>
+</programlisting>
+     The <function>to_regclass()</function> function and its siblings
+     can also be used to perform run-time lookups.  See
+     <xref linkend="functions-info-catalog-table"/>.
+    </para>
+   </note>
+
+   <para>
+    Another practical example of use of <type>regclass</type>
+    is to look up the OID of a table listed in
+    the <literal>information_schema</literal> views, which don't supply
+    such OIDs directly.  One might for example wish to call
+    the <function>pg_relation_size()</function> function, which requires
+    the table OID.  Taking the above rules into account, the correct way
+    to do that is
+<programlisting>
+SELECT table_schema, table_name,
+       pg_relation_size((quote_ident(table_schema) || '.' ||
+                         quote_ident(table_name))::regclass)
+FROM information_schema.tables
+WHERE ...
+</programlisting>
+    The <function>quote_ident()</function> function will take care of
+    double-quoting the identifiers where needed.  The seemingly easier
+<programlisting>
+SELECT pg_relation_size(table_name)
+FROM information_schema.tables
+WHERE ...
+</programlisting>
+    is <emphasis>not recommended</emphasis>, because it will fail for
+    tables that are outside your search path or have names that require
+    quoting.
+   </para>
+
    <para>
     An additional property of most of the OID alias types is the creation of
     dependencies.  If a
@@ -4801,19 +4885,13 @@ SELECT * FROM pg_attribute
     expression <literal>nextval('my_seq'::regclass)</literal>,
     <productname>PostgreSQL</productname>
     understands that the default expression depends on the sequence
-    <literal>my_seq</literal>; the system will not let the sequence be dropped
-    without first removing the default expression.
-    <type>regrole</type> is the only exception for the property. Constants of this
-    type are not allowed in such expressions.
-   </para>
-
-   <note>
-   <para>
-    The OID alias types do not completely follow transaction isolation
-    rules. The planner also treats them as simple constants, which may
-    result in sub-optimal planning.
+    <literal>my_seq</literal>; then the system will not let the sequence
+    be dropped without first removing the default expression.  The
+    alternative of <literal>nextval('my_seq'::text)</literal> does not
+    create a dependency.
+    (<type>regrole</type> is an exception to this property. Constants of this
+    type are not allowed in stored expressions.)
    </para>
-   </note>

    <para>
     Another identifier type used by the system is <type>xid</type>, or transaction
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5ae8abff0c..c60d98360f 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14429,8 +14429,9 @@ SELECT xmltable.*
     <function>table_to_xml</function> maps the content of the named
     table, passed as parameter <parameter>table</parameter>.  The
     <type>regclass</type> type accepts strings identifying tables using the
-    usual notation, including optional schema qualifications and
-    double quotes.  <function>query_to_xml</function> executes the
+    usual notation, including optional schema qualification and
+    double quotes (see <xref linkend="datatype-oid"/> for details).
+    <function>query_to_xml</function> executes the
     query whose text is passed as parameter
     <parameter>query</parameter> and maps the result set.
     <function>cursor_to_xml</function> fetches the indicated number of
@@ -17316,49 +17317,9 @@ SELECT setval('myseq', 42, false);    <lineannotation>Next <function>nextval</fu
    a <type>regclass</type> argument, which is simply the OID of the sequence in the
    <structname>pg_class</structname> system catalog.  You do not have to look up the
    OID by hand, however, since the <type>regclass</type> data type's input
-   converter will do the work for you.  Just write the sequence name enclosed
-   in single quotes so that it looks like a literal constant.  For
-   compatibility with the handling of ordinary
-   <acronym>SQL</acronym> names, the string will be converted to lower case
-   unless it contains double quotes around the sequence name.  Thus:
-<programlisting>
-nextval('foo')      <lineannotation>operates on sequence <literal>foo</literal></lineannotation>
-nextval('FOO')      <lineannotation>operates on sequence <literal>foo</literal></lineannotation>
-nextval('"Foo"')    <lineannotation>operates on sequence <literal>Foo</literal></lineannotation>
-</programlisting>
-   The sequence name can be schema-qualified if necessary:
-<programlisting>
-nextval('myschema.foo')     <lineannotation>operates on <literal>myschema.foo</literal></lineannotation>
-nextval('"myschema".foo')   <lineannotation>same as above</lineannotation>
-nextval('foo')              <lineannotation>searches search path for <literal>foo</literal></lineannotation>
-</programlisting>
-   See <xref linkend="datatype-oid"/> for more information about
-   <type>regclass</type>.
+   converter will do the work for you.  See <xref linkend="datatype-oid"/>
+   for details.
   </para>
-
-  <note>
-   <para>
-    When you write the argument of a sequence function as an unadorned
-    literal string, it becomes a constant of type <type>regclass</type>.
-    Since this is really just an OID, it will track the originally
-    identified sequence despite later renaming, schema reassignment,
-    etc.  This <quote>early binding</quote> behavior is usually desirable for
-    sequence references in column defaults and views.  But sometimes you might
-    want <quote>late binding</quote> where the sequence reference is resolved
-    at run time.  To get late-binding behavior, force the constant to be
-    stored as a <type>text</type> constant instead of <type>regclass</type>:
-<programlisting>
-nextval('foo'::text)      <lineannotation><literal>foo</literal> is looked up at runtime</lineannotation>
-</programlisting>
-   </para>
-
-   <para>
-    Of course, the argument of a sequence function can be an expression
-    as well as a constant.  If it is a text expression then the implicit
-    coercion will result in a run-time lookup.
-   </para>
-  </note>
-
  </sect1>


@@ -26474,11 +26435,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
     <type>regclass</type> argument, which is simply the OID of the table or index
     in the <structname>pg_class</structname> system catalog.  You do not have to look up
     the OID by hand, however, since the <type>regclass</type> data type's input
-    converter will do the work for you.  Just write the table name enclosed in
-    single quotes so that it looks like a literal constant.  For compatibility
-    with the handling of ordinary <acronym>SQL</acronym> names, the string
-    will be converted to lower case unless it contains double quotes around
-    the table name.
+    converter will do the work for you.  See <xref linkend="datatype-oid"/>
+    for details.
    </para>

    <para>