Thread: Patch: regschema OID type

Hey -hackers,

Enclosed is a patch adding a 'regschema' OID type.  I'm really just
hoping to get this out there, don't worry about committing it at this
point.  This is something that I've always wanted in the field (yes,
I'm lazy).  Many thanks to RhodiumToad for pointers about the
necessary system table entries and general advice.

Example usage:

machack:postgres:8555=# select relnamespace::regschema, relname from
pg_class limit 10;
     relnamespace    |           relname
--------------------+------------------------------
  pg_catalog         | pg_type
  pg_catalog         | pg_attribute
  information_schema | foreign_data_wrapper_options
  information_schema | foreign_data_wrappers
  information_schema | _pg_foreign_servers
  information_schema | foreign_server_options
  information_schema | foreign_servers
  information_schema | _pg_user_mappings
  information_schema | user_mapping_options
  information_schema | user_mappings
(10 rows)

It uses the same quoting mechanism as regclass, and I've tested
against some odd schema names such as "foo""schema"; I updated the
docs as I was able, but am not familiar enough with the regression
tests to add those yet.  I hope to address that in a future revision.

Thanks,

David
--
David Christensen
End Point Corporation
david@endpoint.com

On Jan 21, 2010, at 9:46 AM, David Christensen wrote:

> It uses the same quoting mechanism as regclass, and I've tested against some odd schema names such as "foo""schema";
Iupdated the docs as I was able, but am not familiar enough with the regression tests to add those yet.  I hope to
addressthat in a future revision. 

OOh, /me likey! This would save me a ton of code in pgTAP (about half its queries have to join to pg_namespace to get
schemanames). 

Best,

David

David Christensen <david@endpoint.com> writes:
> Enclosed is a patch adding a 'regschema' OID type.

What in the world is the point of that?  The regfoo types are for things
that have schema-qualified names.
        regards, tom lane

"David E. Wheeler" <david@kineticode.com> writes:
> OOh, /me likey! This would save me a ton of code in pgTAP (about half its queries have to join to pg_namespace to get
schemanames).
 

Schema names of what?  It sounds to me like you're failing to use the
existing regfoo types in appropriate places ...
        regards, tom lane

On Jan 21, 2010, at 9:57 AM, Tom Lane wrote:

> Schema names of what?  It sounds to me like you're failing to use the
> existing regfoo types in appropriate places ...

The names of schemas in which to find functions, tables, views, triggers, etc. etc. I have lots of stuff like this:
       SELECT true         FROM pg_catalog.pg_namespace n         JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
     WHERE c.relkind = $1          AND n.nspname = $2          AND c.relname = $3 

I'd love to instead do something like:
       SELECT true         FROM pg_catalog.pg_class c        WHERE c.relkind = $1          AND
c.relnamespace::regschema= $2          AND c.relname = $3 

Best,

David

On Jan 21, 2010, at 11:56 AM, Tom Lane wrote:

> David Christensen <david@endpoint.com> writes:
>> Enclosed is a patch adding a 'regschema' OID type.
>
> What in the world is the point of that?  The regfoo types are for  
> things
> that have schema-qualified names.

Perhaps the naming is a bit disingenuous, and I'm not tied to it; I  
like the ability to translate between oid <-> name that regclass,  
regproc, etc. provide.  This simplifies query lookups and manual  
examination of the system tables and for me at least fills a need.

Do you have a better type name?

Regards,

David
--
David Christensen
End Point Corporation
david@endpoint.com

"David E. Wheeler" <david@kineticode.com> writes:
> The names of schemas in which to find functions, tables, views, triggers, etc. etc. I have lots of stuff like this:

>         SELECT true
>           FROM pg_catalog.pg_namespace n
>           JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
>          WHERE c.relkind = $1
>            AND n.nspname = $2
>            AND c.relname = $3

Well, without a context that explains *why* you're doing that, it's hard
to consider what a better solution would look like.  Personally I
usually prefer solutions involving WHERE oid = 'foo.bar'::regclass,
because that scales easily to either providing or omitting the schema
reference.

If you're trying to avoid throwing an error on bad schema name,
a regschema type would not help you.
        regards, tom lane

On Jan 21, 2010, at 10:06 AM, Tom Lane wrote:

> Well, without a context that explains *why* you're doing that, it's hard
> to consider what a better solution would look like.  Personally I
> usually prefer solutions involving WHERE oid = 'foo.bar'::regclass,
> because that scales easily to either providing or omitting the schema
> reference.

It never occurred to me. And does `oid = bar::regclass` return true if bar is in a schema not in the search path?

But yeah, I need to avoid errors, too.

> If you're trying to avoid throwing an error on bad schema name,
> a regschema type would not help you.

Good point.

Best,

David