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
Attachments:
regschema.patchapplication/octet-stream; name=regschema.patch; x-unix-mode=0644Download
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 56f7a69..24fbc61 100644
*** a/doc/src/sgml/datatype.sgml
--- b/doc/src/sgml/datatype.sgml
*************** SET xmloption TO { DOCUMENT | CONTENT };
*** 4117,4122 ****
--- 4117,4126 ----
</indexterm>
<indexterm zone="datatype-oid">
+ <primary>regschema</primary>
+ </indexterm>
+
+ <indexterm zone="datatype-oid">
<primary>regconfig</primary>
</indexterm>
*************** SET xmloption TO { DOCUMENT | CONTENT };
*** 4146,4152 ****
an object identifier. There are also several alias types for
<type>oid</>: <type>regproc</>, <type>regprocedure</>,
<type>regoper</>, <type>regoperator</>, <type>regclass</>,
! <type>regtype</>, <type>regconfig</>, and <type>regdictionary</>.
<xref linkend="datatype-oid-table"> shows an overview.
</para>
--- 4150,4157 ----
an object identifier. There are also several alias types for
<type>oid</>: <type>regproc</>, <type>regprocedure</>,
<type>regoper</>, <type>regoperator</>, <type>regclass</>,
! <type>regtype</>, <type>regschema</>, <type>regconfig</>,
! and <type>regdictionary</>.
<xref linkend="datatype-oid-table"> shows an overview.
</para>
*************** SELECT * FROM pg_attribute
*** 4256,4261 ****
--- 4261,4273 ----
</row>
<row>
+ <entry><type>regschema</></entry>
+ <entry><structname>pg_namespace</></entry>
+ <entry>schema name</entry>
+ <entry><literal>public</></entry>
+ </row>
+
+ <row>
<entry><type>regconfig</></entry>
<entry><structname>pg_ts_config</></entry>
<entry>text search configuration</entry>
*************** SELECT * FROM pg_attribute
*** 4273,4284 ****
</table>
<para>
! All of the OID alias types 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.
! The <type>regproc</> and <type>regoper</> alias types will only
! accept input names that are unique (not overloaded), so they are
! of limited use; for most uses <type>regprocedure</> or
<type>regoperator</> are more appropriate. For <type>regoperator</>,
unary operators are identified by writing <literal>NONE</> for the unused
operand.
--- 4285,4297 ----
</table>
<para>
! All of the OID alias types (except <type>regschema</>) 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. The <type>regproc</>
! and <type>regoper</> alias types will only accept input names that
! are unique (not overloaded), so they are of limited use; for most
! uses <type>regprocedure</> or
<type>regoperator</> are more appropriate. For <type>regoperator</>,
unary operators are identified by writing <literal>NONE</> for the unused
operand.
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 7214d94..cf2538f 100644
*** a/src/backend/utils/adt/regproc.c
--- b/src/backend/utils/adt/regproc.c
***************
*** 27,32 ****
--- 27,33 ----
#include "catalog/namespace.h"
#include "catalog/pg_class.h"
#include "catalog/pg_operator.h"
+ #include "catalog/pg_namespace.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_ts_config.h"
#include "catalog/pg_ts_dict.h"
*************** regtypesend(PG_FUNCTION_ARGS)
*** 1071,1076 ****
--- 1072,1185 ----
/*
+ * regschemain - converts "nspname" to pg_namespace OID
+ *
+ * We also accept a numeric OID, for symmetry with the output routine.
+ *
+ * '-' signifies unknown (OID 0). In all other cases, the input must
+ * match an existing pg_namespace entry.
+ *
+ * This function is not needed in bootstrap mode, so we don't worry about
+ * making it work then.
+ */
+ Datum
+ regschemain(PG_FUNCTION_ARGS)
+ {
+ char *nsp_name_or_oid = PG_GETARG_CSTRING(0);
+ Oid result;
+ List *names;
+
+ /* '-' ? */
+ if (strcmp(nsp_name_or_oid, "-") == 0)
+ PG_RETURN_OID(InvalidOid);
+
+ /* Numeric OID? */
+ if (nsp_name_or_oid[0] >= '0' &&
+ nsp_name_or_oid[0] <= '9' &&
+ strspn(nsp_name_or_oid, "0123456789") == strlen(nsp_name_or_oid))
+ {
+ result = DatumGetObjectId(DirectFunctionCall1(oidin,
+ CStringGetDatum(nsp_name_or_oid)));
+ PG_RETURN_OID(result);
+ }
+
+ /*
+ * Normal case: lookup the namespace name.
+ */
+
+ names = stringToQualifiedNameList(nsp_name_or_oid);
+ nsp_name_or_oid = strVal(linitial(names));
+
+ result = LookupNamespaceNoError(nsp_name_or_oid);
+ if (result == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("schema \"%s\" does not exist", nsp_name_or_oid)));
+
+ PG_RETURN_OID(result);
+ }
+
+ /*
+ * regschemaout - converts pg_namespace OID to "nspname"
+ */
+ Datum
+ regschemaout(PG_FUNCTION_ARGS)
+ {
+ Oid nspid = PG_GETARG_OID(0);
+ char *result;
+ HeapTuple nsptup;
+
+ if (nspid == InvalidOid)
+ {
+ result = pstrdup("-");
+ PG_RETURN_CSTRING(result);
+ }
+
+ nsptup = SearchSysCache(NAMESPACEOID,
+ ObjectIdGetDatum(nspid),
+ 0, 0, 0);
+
+ if (HeapTupleIsValid(nsptup))
+ {
+ Form_pg_namespace nspform = (Form_pg_namespace) GETSTRUCT(nsptup);
+ char *nspname = NameStr(nspform->nspname);
+
+ result = pstrdup(quote_identifier(nspname));
+
+ ReleaseSysCache(nsptup);
+ }
+ else
+ {
+ /* If OID doesn't match any pg_namespace row, return it numerically */
+ result = (char *) palloc(NAMEDATALEN);
+ snprintf(result, NAMEDATALEN, "%u", nspid);
+ }
+
+ PG_RETURN_CSTRING(result);
+ }
+
+ /*
+ * regschemarecv - converts external binary format to regschema
+ */
+ Datum
+ regschemarecv(PG_FUNCTION_ARGS)
+ {
+ /* Exactly the same as oidrecv, so share code */
+ return oidrecv(fcinfo);
+ }
+
+ /*
+ * regschemasend - converts regschema to binary format
+ */
+ Datum
+ regschemasend(PG_FUNCTION_ARGS)
+ {
+ /* Exactly the same as oidsend, so share code */
+ return oidsend(fcinfo);
+ }
+
+
+ /*
* regconfigin - converts "tsconfigname" to tsconfig OID
*
* We also accept a numeric OID, for symmetry with the output routine.
diff --git a/src/include/catalog/pg_cast.h b/src/include/catalog/pg_cast.h
index cfa14d6..72ee594 100644
*** a/src/include/catalog/pg_cast.h
--- b/src/include/catalog/pg_cast.h
*************** DATA(insert ( 3769 20 1288 a f ));
*** 206,211 ****
--- 206,220 ----
DATA(insert ( 3769 23 0 a b ));
DATA(insert ( 25 2205 1079 i f ));
DATA(insert ( 1043 2205 1079 i f ));
+ DATA(insert ( 26 2212 0 i b ));
+ DATA(insert ( 2212 26 0 i b ));
+ DATA(insert ( 20 2212 1287 i f ));
+ DATA(insert ( 21 2212 313 i f ));
+ DATA(insert ( 23 2212 0 i b ));
+ DATA(insert ( 2212 20 1288 a f ));
+ DATA(insert ( 2212 23 0 a b ));
+ //DATA(insert ( 25 2212 1079 i f ));
+ //DATA(insert ( 1043 2212 1079 i f ));
/*
* String category
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index b93987b..43965da 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 2221 ( regtypeout PG
*** 3579,3584 ****
--- 3579,3589 ----
DESCR("I/O");
DATA(insert OID = 1079 ( regclass PGNSP PGUID 12 1 0 0 f f f t f s 1 0 2205 "25" _null_ _null_ _null_ _null_ text_regclass _null_ _null_ _null_ ));
DESCR("convert text to regclass");
+ DATA(insert OID = 3030 ( regschemain PGNSP PGUID 12 1 0 0 f f f t f s 1 0 2212 "2275" _null_ _null_ _null_ _null_ regschemain _null_ _null_ _null_ ));
+ DATA(insert OID = 3031 ( regschemaout PGNSP PGUID 12 1 0 0 f f f t f s 1 0 2275 "2212" _null_ _null_ _null_ _null_ regschemaout _null_ _null_ _null_ ));
+ DATA(insert OID = 3033 ( regschemarecv PGNSP PGUID 12 1 0 0 f f f t f i 1 0 2212 "2281" _null_ _null_ _null_ _null_ regschemarecv _null_ _null_ _null_ ));
+ DATA(insert OID = 3034 ( regschemasend PGNSP PGUID 12 1 0 0 f f f t f i 1 0 17 "2212" _null_ _null_ _null_ _null_ regschemasend _null_ _null_ _null_ ));
+
DATA(insert OID = 2246 ( fmgr_internal_validator PGNSP PGUID 12 1 0 0 f f f t f s 1 0 2278 "26" _null_ _null_ _null_ _null_ fmgr_internal_validator _null_ _null_ _null_ ));
DESCR("(internal)");
diff --git a/src/include/catalog/pg_type.h b/src/include/catalog/pg_type.h
index c22aacc..6eb9ac0 100644
*** a/src/include/catalog/pg_type.h
--- b/src/include/catalog/pg_type.h
*************** DATA(insert OID = 2210 ( _regclass PG
*** 549,554 ****
--- 549,561 ----
DATA(insert OID = 2211 ( _regtype PGNSP PGUID -1 f b A f t \054 0 2206 0 array_in array_out array_recv array_send - - - i x f 0 -1 0 _null_ _null_ ));
#define REGTYPEARRAYOID 2211
+ DATA(insert OID = 2212 ( regschema PGNSP PGUID 4 t b N f t \054 0 0 2213 regschemain regschemaout regschemarecv regschemasend - - - i p f 0 -1 0 _null_ _null_ ));
+ DESCR("registered schema");
+ #define REGSCHEMAOID 2212
+
+ DATA(insert OID = 2213 ( _regschema PGNSP PGUID -1 f b A f t \054 0 2212 0 array_in array_out array_recv array_send - - - i x f 0 -1 0 _null_ _null_ ));
+ #define REGSCHEMAARRAYOID 2213
+
/* uuid */
DATA(insert OID = 2950 ( uuid PGNSP PGUID 16 f b U f t \054 0 0 2951 uuid_in uuid_out uuid_recv uuid_send - - - c p f 0 -1 0 _null_ _null_ ));
DESCR("UUID datatype");
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 91411a4..9c5f637 100644
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*************** extern Datum regclassin(PG_FUNCTION_ARGS
*** 547,552 ****
--- 547,556 ----
extern Datum regclassout(PG_FUNCTION_ARGS);
extern Datum regclassrecv(PG_FUNCTION_ARGS);
extern Datum regclasssend(PG_FUNCTION_ARGS);
+ extern Datum regschemain(PG_FUNCTION_ARGS);
+ extern Datum regschemaout(PG_FUNCTION_ARGS);
+ extern Datum regschemarecv(PG_FUNCTION_ARGS);
+ extern Datum regschemasend(PG_FUNCTION_ARGS);
extern Datum regtypein(PG_FUNCTION_ARGS);
extern Datum regtypeout(PG_FUNCTION_ARGS);
extern Datum regtyperecv(PG_FUNCTION_ARGS);
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
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 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
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