Skip to content

Commit 0886fc6

Browse files
robertmhaasrobertmhaas
committed
Add new to_reg* functions for error-free OID lookups.
These functions won't throw an error if the object doesn't exist, or if (for functions and operators) there's more than one matching object. Yugo Nagata and Nozomi Anzai, reviewed by Amit Khandekar, Marti Raudsepp, Amit Kapila, and me.
1 parent 7ca32e2 commit 0886fc6

File tree

18 files changed

+457
-19
lines changed

18 files changed

+457
-19
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15279,6 +15279,22 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
1527915279
<primary>collation for</primary>
1528015280
</indexterm>
1528115281

15282+
<indexterm>
15283+
<primary>to_regclass</primary>
15284+
</indexterm>
15285+
15286+
<indexterm>
15287+
<primary>to_regproc</primary>
15288+
</indexterm>
15289+
15290+
<indexterm>
15291+
<primary>to_regoper</primary>
15292+
</indexterm>
15293+
15294+
<indexterm>
15295+
<primary>to_regtype</primary>
15296+
</indexterm>
15297+
1528215298
<para>
1528315299
<xref linkend="functions-info-catalog-table"> lists functions that
1528415300
extract information from the system catalogs.
@@ -15449,6 +15465,26 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
1544915465
<entry><type>text</type></entry>
1545015466
<entry>get the collation of the argument</entry>
1545115467
</row>
15468+
<row>
15469+
<entry><literal><function>to_regclass(<parameter>rel_name</parameter>)</function></literal></entry>
15470+
<entry><type>regclass</type></entry>
15471+
<entry>get the oid of the named relation</entry>
15472+
</row>
15473+
<row>
15474+
<entry><literal><function>to_regproc(<parameter>func_name</parameter>)</function></literal></entry>
15475+
<entry><type>regproc</type></entry>
15476+
<entry>get the oid of the named function</entry>
15477+
</row>
15478+
<row>
15479+
<entry><literal><function>to_regoper(<parameter>operator_name</parameter>)</function></literal></entry>
15480+
<entry><type>regoper</type></entry>
15481+
<entry>get the oid of the named operator</entry>
15482+
</row>
15483+
<row>
15484+
<entry><literal><function>to_regtype(<parameter>type_name</parameter>)</function></literal></entry>
15485+
<entry><type>regtype</type></entry>
15486+
<entry>get the oid of the named type</entry>
15487+
</row>
1545215488
</tbody>
1545315489
</tgroup>
1545415490
</table>
@@ -15614,6 +15650,18 @@ SELECT collation for ('foo' COLLATE "de_DE");
1561415650
is not of a collatable data type, then an error is raised.
1561515651
</para>
1561615652

15653+
<para>
15654+
The <function>to_regclass</function>, <function>to_regproc</function>,
15655+
<function>to_regoper</function> and <function>to_regtype</function>
15656+
translate relation, function, operator, and type names to objects of
15657+
type <type>regclass</>, <type>regproc</>, <type>regoper</> and
15658+
<type>regtype</>, respectively. These functions differ from a cast from
15659+
text in that they don't accept a numeric OID, and that they return null
15660+
rather than throwing an error if the name is not found (or, for
15661+
<function>to_regproc</function> and <function>to_regoper</function>, if
15662+
the given name matches multiple objects).
15663+
</para>
15664+
1561715665
<indexterm>
1561815666
<primary>col_description</primary>
1561915667
</indexterm>

‎src/backend/catalog/namespace.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1556,7 +1556,7 @@ OpernameGetOprid(List *names, Oid oprleft, Oid oprright)
15561556
* will be InvalidOid for a prefix or postfix oprkind. nargs is 2, too.
15571557
*/
15581558
FuncCandidateList
1559-
OpernameGetCandidates(List *names, char oprkind)
1559+
OpernameGetCandidates(List *names, char oprkind, bool missing_schema_ok)
15601560
{
15611561
FuncCandidateList resultList = NULL;
15621562
char *resultSpace = NULL;
@@ -1573,7 +1573,9 @@ OpernameGetCandidates(List *names, char oprkind)
15731573
if (schemaname)
15741574
{
15751575
/* use exact schema given */
1576-
namespaceId = LookupExplicitNamespace(schemaname, false);
1576+
namespaceId = LookupExplicitNamespace(schemaname, missing_schema_ok);
1577+
if (missing_schema_ok && !OidIsValid(namespaceId))
1578+
return NULL;
15771579
}
15781580
else
15791581
{

‎src/backend/parser/parse_oper.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -407,7 +407,7 @@ oper(ParseState *pstate, List *opname, Oid ltypeId, Oid rtypeId,
407407
FuncCandidateList clist;
408408

409409
/* Get binary operators of given name */
410-
clist = OpernameGetCandidates(opname, 'b');
410+
clist = OpernameGetCandidates(opname, 'b', false);
411411

412412
/* No operators found? Then fail... */
413413
if (clist != NULL)
@@ -553,7 +553,7 @@ right_oper(ParseState *pstate, List *op, Oid arg, bool noError, int location)
553553
FuncCandidateList clist;
554554

555555
/* Get postfix operators of given name */
556-
clist = OpernameGetCandidates(op, 'r');
556+
clist = OpernameGetCandidates(op, 'r', false);
557557

558558
/* No operators found? Then fail... */
559559
if (clist != NULL)
@@ -631,7 +631,7 @@ left_oper(ParseState *pstate, List *op, Oid arg, bool noError, int location)
631631
FuncCandidateList clist;
632632

633633
/* Get prefix operators of given name */
634-
clist = OpernameGetCandidates(op, 'l');
634+
clist = OpernameGetCandidates(op, 'l', false);
635635

636636
/* No operators found? Then fail... */
637637
if (clist != NULL)

‎src/backend/parser/parse_type.c

Lines changed: 27 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -706,9 +706,12 @@ pts_error_callback(void *arg)
706706
* Given a string that is supposed to be a SQL-compatible type declaration,
707707
* such as "int4" or "integer" or "character varying(32)", parse
708708
* the string and convert it to a type OID and type modifier.
709+
* If missing_ok is true, InvalidOid is returned rather than raising an error
710+
* when the type name is not found.
709711
*/
710712
void
711-
parseTypeString(const char *str, Oid *typeid_p, int32 *typmod_p)
713+
parseTypeString(const char *str, Oid *typeid_p, int32 *typmod_p,
714+
bool missing_ok)
712715
{
713716
StringInfoData buf;
714717
List *raw_parsetree_list;
@@ -717,6 +720,7 @@ parseTypeString(const char *str, Oid *typeid_p, int32 *typmod_p)
717720
TypeCast *typecast;
718721
TypeName *typeName;
719722
ErrorContextCallback ptserrcontext;
723+
Type tup;
720724

721725
/* make sure we give useful error for empty input */
722726
if (strspn(str, " \t\n\r\f") == strlen(str))
@@ -782,7 +786,28 @@ parseTypeString(const char *str, Oid *typeid_p, int32 *typmod_p)
782786
if (typeName->setof)
783787
goto fail;
784788

785-
typenameTypeIdAndMod(NULL, typeName, typeid_p, typmod_p);
789+
tup = LookupTypeName(NULL, typeName, typmod_p, missing_ok);
790+
if (tup == NULL)
791+
{
792+
if (!missing_ok)
793+
ereport(ERROR,
794+
(errcode(ERRCODE_UNDEFINED_OBJECT),
795+
errmsg("type \"%s\" does not exist",
796+
TypeNameToString(typeName)),
797+
parser_errposition(NULL, typeName->location)));
798+
*typeid_p = InvalidOid;
799+
}
800+
else
801+
{
802+
if (!((Form_pg_type) GETSTRUCT(tup))->typisdefined)
803+
ereport(ERROR,
804+
(errcode(ERRCODE_UNDEFINED_OBJECT),
805+
errmsg("type \"%s\" is only a shell",
806+
TypeNameToString(typeName)),
807+
parser_errposition(NULL, typeName->location)));
808+
*typeid_p = HeapTupleGetOid(tup);
809+
ReleaseSysCache(tup);
810+
}
786811

787812
pfree(buf.data);
788813

‎src/backend/utils/adt/regproc.c

Lines changed: 104 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -152,6 +152,31 @@ regprocin(PG_FUNCTION_ARGS)
152152
PG_RETURN_OID(result);
153153
}
154154

155+
/*
156+
* to_regproc - converts "proname" to proc OID
157+
*
158+
* If the name is not found, we return NULL.
159+
*/
160+
Datum
161+
to_regproc(PG_FUNCTION_ARGS)
162+
{
163+
char *pro_name = PG_GETARG_CSTRING(0);
164+
List *names;
165+
FuncCandidateList clist;
166+
167+
/*
168+
* Parse the name into components and see if it matches any pg_proc entries
169+
* in the current search path.
170+
*/
171+
names = stringToQualifiedNameList(pro_name);
172+
clist = FuncnameGetCandidates(names, -1, NIL, false, false, true);
173+
174+
if (clist == NULL || clist->next != NULL)
175+
PG_RETURN_NULL();
176+
177+
PG_RETURN_OID(clist->oid);
178+
}
179+
155180
/*
156181
* regprocout - converts proc OID to "pro_name"
157182
*/
@@ -502,7 +527,7 @@ regoperin(PG_FUNCTION_ARGS)
502527
* pg_operator entries in the current search path.
503528
*/
504529
names = stringToQualifiedNameList(opr_name_or_oid);
505-
clist = OpernameGetCandidates(names, '\0');
530+
clist = OpernameGetCandidates(names, '\0', false);
506531

507532
if (clist == NULL)
508533
ereport(ERROR,
@@ -519,6 +544,31 @@ regoperin(PG_FUNCTION_ARGS)
519544
PG_RETURN_OID(result);
520545
}
521546

547+
/*
548+
* to_regoper - converts "oprname" to operator OID
549+
*
550+
* If the name is not found, we return NULL.
551+
*/
552+
Datum
553+
to_regoper(PG_FUNCTION_ARGS)
554+
{
555+
char *opr_name = PG_GETARG_CSTRING(0);
556+
List *names;
557+
FuncCandidateList clist;
558+
559+
/*
560+
* Parse the name into components and see if it matches any pg_operator
561+
* entries in the current search path.
562+
*/
563+
names = stringToQualifiedNameList(opr_name);
564+
clist = OpernameGetCandidates(names, '\0', true);
565+
566+
if (clist == NULL || clist->next != NULL)
567+
PG_RETURN_NULL();
568+
569+
PG_RETURN_OID(clist->oid);
570+
}
571+
522572
/*
523573
* regoperout - converts operator OID to "opr_name"
524574
*/
@@ -558,7 +608,7 @@ regoperout(PG_FUNCTION_ARGS)
558608
* qualify it.
559609
*/
560610
clist = OpernameGetCandidates(list_make1(makeString(oprname)),
561-
'\0');
611+
'\0', false);
562612
if (clist != NULL && clist->next == NULL &&
563613
clist->oid == oprid)
564614
result = pstrdup(oprname);
@@ -872,6 +922,33 @@ regclassin(PG_FUNCTION_ARGS)
872922
PG_RETURN_OID(result);
873923
}
874924

925+
/*
926+
* to_regclass - converts "classname" to class OID
927+
*
928+
* If the name is not found, we return NULL.
929+
*/
930+
Datum
931+
to_regclass(PG_FUNCTION_ARGS)
932+
{
933+
char *class_name = PG_GETARG_CSTRING(0);
934+
Oid result;
935+
List *names;
936+
937+
/*
938+
* Parse the name into components and see if it matches any pg_class entries
939+
* in the current search path.
940+
*/
941+
names = stringToQualifiedNameList(class_name);
942+
943+
/* We might not even have permissions on this relation; don't lock it. */
944+
result = RangeVarGetRelid(makeRangeVarFromNameList(names), NoLock, true);
945+
946+
if (OidIsValid(result))
947+
PG_RETURN_OID(result);
948+
else
949+
PG_RETURN_NULL();
950+
}
951+
875952
/*
876953
* regclassout - converts class OID to "class_name"
877954
*/
@@ -1028,11 +1105,34 @@ regtypein(PG_FUNCTION_ARGS)
10281105
* Normal case: invoke the full parser to deal with special cases such as
10291106
* array syntax.
10301107
*/
1031-
parseTypeString(typ_name_or_oid, &result, &typmod);
1108+
parseTypeString(typ_name_or_oid, &result, &typmod, false);
10321109

10331110
PG_RETURN_OID(result);
10341111
}
10351112

1113+
/*
1114+
* to_regtype - converts "typename" to type OID
1115+
*
1116+
* If the name is not found, we return NULL.
1117+
*/
1118+
Datum
1119+
to_regtype(PG_FUNCTION_ARGS)
1120+
{
1121+
char *typ_name = PG_GETARG_CSTRING(0);
1122+
Oid result;
1123+
int32 typmod;
1124+
1125+
/*
1126+
* Invoke the full parser to deal with special cases such as array syntax.
1127+
*/
1128+
parseTypeString(typ_name, &result, &typmod, true);
1129+
1130+
if (OidIsValid(result))
1131+
PG_RETURN_OID(result);
1132+
else
1133+
PG_RETURN_NULL();
1134+
}
1135+
10361136
/*
10371137
* regtypeout - converts type OID to "typ_name"
10381138
*/
@@ -1523,7 +1623,7 @@ parseNameAndArgTypes(const char *string, bool allowNone, List **names,
15231623
else
15241624
{
15251625
/* Use full parser to resolve the type name */
1526-
parseTypeString(typename, &typeid, &typmod);
1626+
parseTypeString(typename, &typeid, &typmod, false);
15271627
}
15281628
if (*nargs >= FUNC_MAX_ARGS)
15291629
ereport(ERROR,

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201404031
56+
#define CATALOG_VERSION_NO 201404081
5757

5858
#endif

‎src/include/catalog/namespace.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -76,7 +76,8 @@ extern FuncCandidateList FuncnameGetCandidates(List *names,
7676
extern bool FunctionIsVisible(Oid funcid);
7777

7878
extern Oid OpernameGetOprid(List *names, Oid oprleft, Oid oprright);
79-
extern FuncCandidateList OpernameGetCandidates(List *names, char oprkind);
79+
extern FuncCandidateList OpernameGetCandidates(List *names, char oprkind,
80+
bool missing_schema_ok);
8081
extern bool OperatorIsVisible(Oid oprid);
8182

8283
extern Oid OpclassnameGetOpcid(Oid amid, const char *opcname);

‎src/include/catalog/pg_proc.h

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -173,6 +173,8 @@ DATA(insert OID = 44 ( regprocin PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0
173173
DESCR("I/O");
174174
DATA(insert OID = 45 ( regprocout PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2275 "24" _null_ _null_ _null_ _null_ regprocout _null_ _null_ _null_ ));
175175
DESCR("I/O");
176+
DATA(insert OID = 3494 ( to_regproc PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 24 "2275" _null_ _null_ _null_ _null_ to_regproc _null_ _null_ _null_ ));
177+
DESCR("convert proname to regproc");
176178
DATA(insert OID = 46 ( textin PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "2275" _null_ _null_ _null_ _null_ textin _null_ _null_ _null_ ));
177179
DESCR("I/O");
178180
DATA(insert OID = 47 ( textout PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2275 "25" _null_ _null_ _null_ _null_ textout _null_ _null_ _null_ ));
@@ -3304,6 +3306,8 @@ DATA(insert OID = 2214 ( regoperin PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2
33043306
DESCR("I/O");
33053307
DATA(insert OID = 2215 ( regoperout PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2275 "2203" _null_ _null_ _null_ _null_ regoperout _null_ _null_ _null_ ));
33063308
DESCR("I/O");
3309+
DATA(insert OID = 3492 ( to_regoper PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2203 "2275" _null_ _null_ _null_ _null_ to_regoper _null_ _null_ _null_ ));
3310+
DESCR("convert operator name to regoper");
33073311
DATA(insert OID = 2216 ( regoperatorin PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2204 "2275" _null_ _null_ _null_ _null_ regoperatorin _null_ _null_ _null_ ));
33083312
DESCR("I/O");
33093313
DATA(insert OID = 2217 ( regoperatorout PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2275 "2204" _null_ _null_ _null_ _null_ regoperatorout _null_ _null_ _null_ ));
@@ -3312,10 +3316,14 @@ DATA(insert OID = 2218 ( regclassin PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2
33123316
DESCR("I/O");
33133317
DATA(insert OID = 2219 ( regclassout PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2275 "2205" _null_ _null_ _null_ _null_ regclassout _null_ _null_ _null_ ));
33143318
DESCR("I/O");
3319+
DATA(insert OID = 3495 ( to_regclass PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2205 "2275" _null_ _null_ _null_ _null_ to_regclass _null_ _null_ _null_ ));
3320+
DESCR("convert classname to regclass");
33153321
DATA(insert OID = 2220 ( regtypein PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2206 "2275" _null_ _null_ _null_ _null_ regtypein _null_ _null_ _null_ ));
33163322
DESCR("I/O");
33173323
DATA(insert OID = 2221 ( regtypeout PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2275 "2206" _null_ _null_ _null_ _null_ regtypeout _null_ _null_ _null_ ));
33183324
DESCR("I/O");
3325+
DATA(insert OID = 3493 ( to_regtype PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2206 "2275" _null_ _null_ _null_ _null_ to_regtype _null_ _null_ _null_ ));
3326+
DESCR("convert type name to regtype");
33193327
DATA(insert OID = 1079 ( regclass PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2205 "25" _null_ _null_ _null_ _null_ text_regclass _null_ _null_ _null_ ));
33203328
DESCR("convert text to regclass");
33213329

‎src/include/parser/parse_type.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -47,7 +47,7 @@ extern Datum stringTypeDatum(Type tp, char *string, int32 atttypmod);
4747

4848
extern Oid typeidTypeRelid(Oid type_id);
4949

50-
extern void parseTypeString(const char *str, Oid *typeid_p, int32 *typmod_p);
50+
extern void parseTypeString(const char *str, Oid *typeid_p, int32 *typmod_p, bool missing_ok);
5151

5252
#define ISCOMPLEX(typeid) (typeidTypeRelid(typeid) != InvalidOid)
5353

0 commit comments

Comments
 (0)