[ redirecting to pgsql-docs list ]
I wrote:
> Federico 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
-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, myschema.mytable is acceptable input
+for regclass (if there is such a table). That value
+might be output as myschema.mytable, or
+just mytable, depending on the current search path.
The regproc and regoper alias types will only
accept input names that are unique (not overloaded), so they are
of limited use; for most uses regprocedure or
@@ -4792,6 +4796,86 @@ SELECT * FROM pg_attribute
operand.
+
+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 Foo (with
+upper case F) taking two integer arguments could be
+entered as ' "Foo" ( int, integer ) '::regprocedure.
+The output would look like "Foo"(integer,integer).
+Both the function name and the argument type names could be
+schema-qualified, too.
+
+
+
+Many built-in PostgreSQL functions accept
+the OID of a table, or another kind of database object, and for
+convenience are declared as taking regclass (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 nextval() function
+takes a sequence relation's OID, so you could call it like this:
+
+nextval('foo') operates on sequence foo
+nextval('FOO') same as above
+nextval('"Foo"')operates on sequence Foo
+nextval('myschema.foo') operates on myschema.foo
+nextval('"myschema".foo') same as above
+nextval('foo') searches search path for foo
+
+
+
+
+
+ When you write the argument of such a function as an unadorned
+ literal string, it becomes a constant of type regclass
+ (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 early binding behavior is usually desirable for
+ object references in column defaults and views. But sometimes you might
+ want late binding where the object reference is resolved
+ at run time. To get late-binding behavior, force the constant to be
+ stored as a text constant instead of regclass:
+
+nextval('foo'::text) foo is looked up at runtime
+
+ The to_regclass() function and its siblings
+ can also be used to perform run-time lookups. See
+ .
+
+
+
+
+Another practical example of use of regclass
+is to look up the OID of a table listed in
+the information_schema views, which don't supply
+such OIDs directly. One might for example wish to call
+the pg_relation_size() function, which requires
+the table OID. Taking the above rules into account, the correct way
+to do that is
+
+SELECT table_schema, table_name,
+ pg_relation_size((quote_ident(table_schema) || '.' ||
+ quote_ident(table_name))::regclass)
+FROM information_schema.tables
+WHERE ...
+
+The quote_ident() function will take care of
+double-quoting the identifiers where needed. The seemingly easier
+
+SELECT pg_relation_size(table_na
