Horton
Menu

Thread

  1. Patch: regschema OID type

    David Christensen <david@endpoint.com> — 2010-01-21T17:46:47Z

    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

  2. Re: Patch: regschema OID type

    David E. Wheeler <david@kineticode.com> — 2010-01-21T17:52:40Z

    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"; 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.

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 schema names).

Best,

David

  3. Re: Patch: regschema OID type

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-01-21T17:56:11Z

    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

  4. Re: Patch: regschema OID type

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-01-21T17:57:35Z

    "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 schema names).

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

			regards, tom lane

  5. Re: Patch: regschema OID type

    David E. Wheeler <david@kineticode.com> — 2010-01-21T18:02:14Z

    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

  6. Re: Patch: regschema OID type

    David Christensen <david@endpoint.com> — 2010-01-21T18:04:19Z

    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

  7. Re: Patch: regschema OID type

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-01-21T18:06:20Z

    "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

  8. Re: Patch: regschema OID type

    David E. Wheeler <david@kineticode.com> — 2010-01-21T18:11:01Z

    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