Hi
I miss a reglanguage type from our set of reg* types.
It reduce a mental overhead for queries over pg_proc table
With this type I can easy filter only plpgsql functions
select *
from pg_proc
where prolang = 'plpgsql'::reglanguage
and pronamespace <> 'pg_catalog'::regnamespace;
Regards
Pavel
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index d1d033178f..85bcd43d3d 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4525,8 +4525,8 @@ INSERT INTO mytable VALUES(-1); -- fails
several alias types for <type>oid</type>: <type>regproc</type>,
<type>regprocedure</type>, <type>regoper</type>, <type>regoperator</type>,
<type>regclass</type>, <type>regtype</type>, <type>regrole</type>,
- <type>regnamespace</type>, <type>regconfig</type>, and
- <type>regdictionary</type>. <xref linkend="datatype-oid-table"/> shows an
+ <type>regnamespace</type>, <type>regconfig</type>, <type>reglanguage</type>
+ and <type>regdictionary</type>. <xref linkend="datatype-oid-table"/> shows an
overview.
</para>
@@ -4660,6 +4660,14 @@ SELECT * FROM pg_attribute
<entry>text search dictionary</entry>
<entry><literal>simple</literal></entry>
</row>
+
+ <row>
+ <entry><type>reglanguage</type></entry>
+ <entry><structname>pg_language</structname></entry>
+ <entry>language name</entry>
+ <entry><literal>plpgsql</literal></entry>
+ </row>
+
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 28035f1635..2bb1b2fd8e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18413,6 +18413,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
<entry><type>regrole</type></entry>
<entry>get the OID of the named role</entry>
</row>
+ <row>
+ <entry><literal><function>to_reglanguage(<parameter>language_name</parameter>)</function></literal></entry>
+ <entry><type>regrole</type></entry>
+ <entry>get the OID of the named language</entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -18717,7 +18722,7 @@ SELECT collation for ('foo' COLLATE "de_DE");
names (given as <type>text</type>) to objects of
type <type>regclass</type>, <type>regproc</type>, <type>regprocedure</type>,
<type>regoper</type>, <type>regoperator</type>, <type>regtype</type>,
- <type>regnamespace</type>, and <type>regrole</type>
+ <type>regnamespace</type>, <type>regrole</type> and <type>reglanguage</type>
respectively. These functions differ from a cast from
text in that they don't accept a numeric OID, and that they return null
rather than throwing an error if the name is not found (or, for
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index f0fa52bc27..da412ef01b 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -29,6 +29,7 @@
#include "catalog/pg_ts_config.h"
#include "catalog/pg_ts_dict.h"
#include "catalog/pg_type.h"
+#include "commands/proclang.h"
#include "lib/stringinfo.h"
#include "miscadmin.h"
#include "parser/parse_type.h"
@@ -1717,6 +1718,118 @@ stringToQualifiedNameList(const char *string)
return result;
}
+/*
+ * reglanguagein - converts "language" to type OID
+ *
+ * We also accept a numeric OID, for symmetry with the output routine,
+ * and for possible use in bootstrap mode.
+ *
+ * '-' signifies unknown (OID 0). In all other cases, the input must
+ * match an existing pg_type entry.
+ */
+Datum
+reglanguagein(PG_FUNCTION_ARGS)
+{
+ char *language_name_or_oid = PG_GETARG_CSTRING(0);
+ Oid result = InvalidOid;
+
+ /* '-' ? */
+ if (strcmp(language_name_or_oid, "-") == 0)
+ PG_RETURN_OID(InvalidOid);
+
+ /* Numeric OID? */
+ if (language_name_or_oid[0] >= '0' &&
+ language_name_or_oid[0] <= '9' &&
+ strspn(language_name_or_oid, "0123456789") == strlen(language_name_or_oid))
+ {
+ result = DatumGetObjectId(DirectFunctionCall1(oidin,
+ CStringGetDatum(language_name_or_oid)));
+ PG_RETURN_OID(result);
+ }
+
+ /* Else it's a type name, possibly schema-qualified or decorated */
+
+ /* The rest of this wouldn't work in bootstrap mode */
+ if (IsBootstrapProcessingMode())
+ elog(ERROR, "reglanguage values must be OIDs in bootstrap mode");
+
+ result = get_language_oid(language_name_or_oid, false);
+
+ PG_RETURN_OID(result);
+}
+
+/*
+ * to_reglanguage - converts "language nane" to type OID
+ *
+ * If the name is not found, we return NULL.
+ */
+Datum
+to_reglanguage(PG_FUNCTION_ARGS)
+{
+ char *language_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ Oid result;
+
+ result = get_language_oid(language_name, true);
+
+ if (OidIsValid(result))
+ PG_RETURN_OID(result);
+ else
+ PG_RETURN_NULL();
+}
+
+/*
+ * reglanguageout - converts language OID to "language_name"
+ */
+Datum
+reglanguageout(PG_FUNCTION_ARGS)
+{
+ Oid langid = PG_GETARG_OID(0);
+ char *result;
+
+ if (langid == InvalidOid)
+ {
+ result = pstrdup("-");
+ PG_RETURN_CSTRING(result);
+ }
+
+ result = get_language_name(langid, true);
+
+ if (result)
+ {
+ /* pstrdup is not really necessary, but it avoids a compiler warning */
+ result = pstrdup(quote_identifier(result));
+ }
+ else
+ {
+ /* If OID doesn't match any namespace, return it numerically */
+ result = (char *) palloc(NAMEDATALEN);
+ snprintf(result, NAMEDATALEN, "%u", langid);
+ }
+
+ PG_RETURN_CSTRING(result);
+}
+
+/*
+ * reglanguagerecv - converts external binary format to reglanguage
+ */
+Datum
+reglanguagerecv(PG_FUNCTION_ARGS)
+{
+ /* Exactly the same as oidrecv, so share code */
+ return oidrecv(fcinfo);
+}
+
+/*
+ * reglanguagesend - converts reglanguage to binary format
+ */
+Datum
+reglanguagesend(PG_FUNCTION_ARGS)
+{
+ /* Exactly the same as oidsend, so share code */
+ return oidsend(fcinfo);
+}
+
+
/*****************************************************************************
* SUPPORT ROUTINES *
*****************************************************************************/
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 0be26fe037..934bc5215a 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -3760,6 +3760,7 @@ convert_to_scalar(Datum value, Oid valuetypid, Oid collid, double *scaledvalue,
case REGDICTIONARYOID:
case REGROLEOID:
case REGNAMESPACEOID:
+ case REGLANGUAGEOID:
*scaledvalue = convert_numeric_to_scalar(value, valuetypid,
&failure);
*scaledlobound = convert_numeric_to_scalar(lobound, boundstypid,
@@ -3891,6 +3892,7 @@ convert_numeric_to_scalar(Datum value, Oid typid, bool *failure)
case REGDICTIONARYOID:
case REGROLEOID:
case REGNAMESPACEOID:
+ case REGLANGUAGEOID:
/* we can treat OIDs as integers... */
return (double) DatumGetObjectId(value);
}
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 5f9a102a74..0fa52b33a7 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -1034,9 +1034,11 @@ check_for_reg_data_type_usage(ClusterInfo *cluster)
" 'regoper', "
" 'regoperator', "
" 'regproc', "
- " 'regprocedure' "
+ " 'regprocedure', "
+ " 'reglanguage' "
/* regrole.oid is preserved, so 'regrole' is OK */
/* regtype.oid is preserved, so 'regtype' is OK */
+ /* reglanguage.oid is preserved, so 'reglanguage' is OK */
" ) AND "
" c.relnamespace = n.oid AND "
" n.nspname NOT IN ('pg_catalog', 'information_schema')");
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index 6ef8b8a4e7..ef75611587 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -263,6 +263,20 @@
castcontext => 'a', castmethod => 'f' },
{ castsource => 'regnamespace', casttarget => 'int4', castfunc => '0',
castcontext => 'a', castmethod => 'b' },
+{ castsource => 'oid', casttarget => 'reglanguage', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'reglanguage', casttarget => 'oid', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'int8', casttarget => 'reglanguage', castfunc => 'oid',
+ castcontext => 'i', castmethod => 'f' },
+{ castsource => 'int2', casttarget => 'reglanguage', castfunc => 'int4(int2)',
+ castcontext => 'i', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'reglanguage', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'reglanguage', casttarget => 'int8', castfunc => 'int8(oid)',
+ castcontext => 'a', castmethod => 'f' },
+{ castsource => 'reglanguage', casttarget => 'int4', castfunc => '0',
+ castcontext => 'a', castmethod => 'b' },
# String category
{ castsource => 'text', casttarget => 'bpchar', castfunc => '0',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 07a86c7b7b..1cba01c3c9 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6700,6 +6700,17 @@
prorettype => 'regnamespace', proargtypes => 'text',
prosrc => 'to_regnamespace' },
+{ oid => '4191', descr => 'I/O',
+ proname => 'reglanguagein', provolatile => 's', prorettype => 'reglanguage',
+ proargtypes => 'cstring', prosrc => 'reglanguagein' },
+{ oid => '4192', descr => 'I/O',
+ proname => 'reglanguageout', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'reglanguage', prosrc => 'reglanguageout' },
+{ oid => '4193', descr => 'convert language name to reglanguage',
+ proname => 'to_reglanguage', provolatile => 's',
+ prorettype => 'reglanguage', proargtypes => 'text',
+ prosrc => 'to_reglanguage' },
+
{ oid => '1268',
descr => 'parse qualified identifier to array of identifiers',
proname => 'parse_ident', prorettype => '_text', proargtypes => 'text bool',
@@ -7471,6 +7482,12 @@
{ oid => '4088', descr => 'I/O',
proname => 'regnamespacesend', prorettype => 'bytea',
proargtypes => 'regnamespace', prosrc => 'regnamespacesend' },
+{ oid => '4194', descr => 'I/O',
+ proname => 'reglanguagerecv', prorettype => 'reglanguage',
+ proargtypes => 'internal', prosrc => 'reglanguagerecv' },
+{ oid => '4195', descr => 'I/O',
+ proname => 'reglanguagesend', prorettype => 'bytea',
+ proargtypes => 'reglanguage', prosrc => 'reglanguagesend' },
{ oid => '2456', descr => 'I/O',
proname => 'bit_recv', prorettype => 'bit',
proargtypes => 'internal oid int4', prosrc => 'bit_recv' },
diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat
index 4cf2b9df7b..5502c7a622 100644
--- a/src/include/catalog/pg_type.dat
+++ b/src/include/catalog/pg_type.dat
@@ -392,6 +392,11 @@
typinput => 'regnamespacein', typoutput => 'regnamespaceout',
typreceive => 'regnamespacerecv', typsend => 'regnamespacesend',
typalign => 'i' },
+{ oid => '4179', array_type_oid => '4180', descr => 'registered language',
+ typname => 'reglanguage', typlen => '4', typbyval => 't', typcategory => 'N',
+ typinput => 'reglanguagein', typoutput => 'reglanguageout',
+ typreceive => 'reglanguagerecv', typsend => 'reglanguagesend',
+ typalign => 'i' },
# uuid
{ oid => '2950', array_type_oid => '2951', descr => 'UUID datatype',
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index ee4fcda866..198716bc8e 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -40,6 +40,12 @@ SELECT regtype('int4');
integer
(1 row)
+SELECT reglanguage('plpgsql');
+ reglanguage
+-------------
+ plpgsql
+(1 row)
+
SELECT to_regoper('||/');
to_regoper
------------
@@ -76,6 +82,12 @@ SELECT to_regtype('int4');
integer
(1 row)
+SELECT to_reglanguage('plpgsql');
+ to_reglanguage
+----------------
+ plpgsql
+(1 row)
+
-- with schemaname
SELECT regoper('pg_catalog.||/');
regoper
@@ -219,6 +231,10 @@ SELECT regtype('int3');
ERROR: type "int3" does not exist
LINE 1: SELECT regtype('int3');
^
+SELECT reglanguage('plpgsq');
+ERROR: language "plpgsq" does not exist
+LINE 1: SELECT reglanguage('plpgsq');
+ ^
-- with schemaname
SELECT regoper('ng_catalog.||/');
ERROR: schema "ng_catalog" does not exist
@@ -277,6 +293,10 @@ SELECT regnamespace('foo.bar');
ERROR: invalid name syntax
LINE 1: SELECT regnamespace('foo.bar');
^
+SELECT reglanguage('xxx.plpgsq');
+ERROR: language "xxx.plpgsq" does not exist
+LINE 1: SELECT reglanguage('xxx.plpgsq');
+ ^
/* If objects don't exist, return NULL with no error. */
-- without schemaname
SELECT to_regoper('||//');
@@ -315,6 +335,12 @@ SELECT to_regtype('int3');
(1 row)
+SELECT to_reglanguage('plpgsq');
+ to_reglanguage
+----------------
+
+(1 row)
+
-- with schemaname
SELECT to_regoper('ng_catalog.||/');
to_regoper
@@ -395,3 +421,9 @@ SELECT to_regnamespace('"Nonexistent"');
SELECT to_regnamespace('foo.bar');
ERROR: invalid name syntax
+SELECT to_reglanguage('xxx.plpgsq');
+ to_reglanguage
+----------------
+
+(1 row)
+
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index a60bc28901..13daaa5fc1 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -14,6 +14,7 @@ SELECT regproc('now');
SELECT regprocedure('abs(numeric)');
SELECT regclass('pg_class');
SELECT regtype('int4');
+SELECT reglanguage('plpgsql');
SELECT to_regoper('||/');
SELECT to_regoperator('+(int4,int4)');
@@ -21,6 +22,7 @@ SELECT to_regproc('now');
SELECT to_regprocedure('abs(numeric)');
SELECT to_regclass('pg_class');
SELECT to_regtype('int4');
+SELECT to_reglanguage('plpgsql');
-- with schemaname
@@ -61,6 +63,7 @@ SELECT regproc('know');
SELECT regprocedure('absinthe(numeric)');
SELECT regclass('pg_classes');
SELECT regtype('int3');
+SELECT reglanguage('plpgsq');
-- with schemaname
@@ -81,6 +84,7 @@ SELECT regrole('foo.bar');
SELECT regnamespace('Nonexistent');
SELECT regnamespace('"Nonexistent"');
SELECT regnamespace('foo.bar');
+SELECT reglanguage('xxx.plpgsq');
/* If objects don't exist, return NULL with no error. */
@@ -92,6 +96,7 @@ SELECT to_regproc('know');
SELECT to_regprocedure('absinthe(numeric)');
SELECT to_regclass('pg_classes');
SELECT to_regtype('int3');
+SELECT to_reglanguage('plpgsq');
-- with schemaname
@@ -113,3 +118,4 @@ SELECT to_regrole('foo.bar');
SELECT to_regnamespace('Nonexistent');
SELECT to_regnamespace('"Nonexistent"');
SELECT to_regnamespace('foo.bar');
+SELECT to_reglanguage('xxx.plpgsq');
