Add transforms feature

This provides a mechanism for specifying conversions between SQL data
types and procedural languages.  As examples, there are transforms
for hstore and ltree for PL/Perl and PL/Python.

reviews by Pavel Stěhule and Andres Freund

6 files changed, 141 insertions, 14 deletions

diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index a403c64360..c73f20d6a5 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -41,6 +41,7 @@ POSTGRES_BKI_SRCS = $(addprefix $(top_srcdir)/src/include/catalog/,\
 	pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \
 	pg_foreign_table.h pg_policy.h \
 	pg_default_acl.h pg_seclabel.h pg_shseclabel.h pg_collation.h pg_range.h \
+	pg_transform.h \
 	toasting.h indexing.h \
     )
 
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index bacb242e4e..6271f8f5a0 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -47,6 +47,7 @@
 #include "catalog/pg_proc.h"
 #include "catalog/pg_rewrite.h"
 #include "catalog/pg_tablespace.h"
+#include "catalog/pg_transform.h"
 #include "catalog/pg_trigger.h"
 #include "catalog/pg_ts_config.h"
 #include "catalog/pg_ts_dict.h"
@@ -1265,6 +1266,10 @@ doDeletion(const ObjectAddress *object, int flags)
 			RemovePolicyById(object->objectId);
 			break;
 
+		case OCLASS_TRANSFORM:
+			DropTransformById(object->objectId);
+			break;
+
 		default:
 			elog(ERROR, "unrecognized object class: %u",
 				 object->classId);
@@ -2373,6 +2378,9 @@ getObjectClass(const ObjectAddress *object)
 
 		case PolicyRelationId:
 			return OCLASS_POLICY;
+
+		case TransformRelationId:
+			return OCLASS_TRANSFORM;
 	}
 
 	/* shouldn't get here */
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 3c6a049b6d..6e1b241956 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1928,7 +1928,39 @@ GRANT SELECT ON tables TO PUBLIC;
  * TRANSFORMS view
  */
 
--- feature not supported
+CREATE VIEW transforms AS
+    SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
+           CAST(nt.nspname AS sql_identifier) AS udt_schema,
+           CAST(t.typname AS sql_identifier) AS udt_name,
+           CAST(current_database() AS sql_identifier) AS specific_catalog,
+           CAST(np.nspname AS sql_identifier) AS specific_schema,
+           CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
+           CAST(l.lanname AS sql_identifier) AS group_name,
+           CAST('FROM SQL' AS character_data) AS transform_type
+    FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype
+         JOIN pg_language l ON x.trflang = l.oid
+         JOIN pg_proc p ON x.trffromsql = p.oid
+         JOIN pg_namespace nt ON t.typnamespace = nt.oid
+         JOIN pg_namespace np ON p.pronamespace = np.oid
+
+  UNION
+
+    SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
+           CAST(nt.nspname AS sql_identifier) AS udt_schema,
+           CAST(t.typname AS sql_identifier) AS udt_name,
+           CAST(current_database() AS sql_identifier) AS specific_catalog,
+           CAST(np.nspname AS sql_identifier) AS specific_schema,
+           CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
+           CAST(l.lanname AS sql_identifier) AS group_name,
+           CAST('TO SQL' AS character_data) AS transform_type
+    FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype
+         JOIN pg_language l ON x.trflang = l.oid
+         JOIN pg_proc p ON x.trftosql = p.oid
+         JOIN pg_namespace nt ON t.typnamespace = nt.oid
+         JOIN pg_namespace np ON p.pronamespace = np.oid
+
+  ORDER BY udt_catalog, udt_schema, udt_name, group_name, transform_type  -- some sensible grouping for interactive use
+;
 
 
 /*
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 30cb699072..5e1bda4ed2 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -45,6 +45,7 @@
 #include "catalog/pg_policy.h"
 #include "catalog/pg_rewrite.h"
 #include "catalog/pg_tablespace.h"
+#include "catalog/pg_transform.h"
 #include "catalog/pg_trigger.h"
 #include "catalog/pg_ts_config.h"
 #include "catalog/pg_ts_dict.h"
@@ -335,6 +336,12 @@ static const ObjectPropertyType ObjectProperty[] =
 		true
 	},
 	{
+		TransformRelationId,
+		TransformOidIndexId,
+		TRFOID,
+		InvalidAttrNumber
+	},
+	{
 		TriggerRelationId,
 		TriggerOidIndexId,
 		-1,
@@ -760,6 +767,19 @@ get_object_address(ObjectType objtype, List *objname, List *objargs,
 					address.objectSubId = 0;
 				}
 				break;
+			case OBJECT_TRANSFORM:
+				{
+					TypeName   *typename = (TypeName *) linitial(objname);
+					char	   *langname = (char *) linitial(objargs);
+					Oid			type_id = LookupTypeNameOid(NULL, typename, missing_ok);
+					Oid			lang_id = get_language_oid(langname, missing_ok);
+
+					address.classId = TransformRelationId;
+					address.objectId =
+						get_transform_oid(type_id, lang_id, missing_ok);
+					address.objectSubId = 0;
+				}
+				break;
 			case OBJECT_TSPARSER:
 				address.classId = TSParserRelationId;
 				address.objectId = get_ts_parser_oid(objname, missing_ok);
@@ -2006,6 +2026,15 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
 									format_type_be(targettypeid))));
 			}
 			break;
+		case OBJECT_TRANSFORM:
+			{
+				TypeName   *typename = (TypeName *) linitial(objname);
+				Oid			typeid = typenameTypeId(NULL, typename);
+
+				if (!pg_type_ownercheck(typeid, roleid))
+					aclcheck_error_type(ACLCHECK_NOT_OWNER, typeid);
+			}
+			break;
 		case OBJECT_TABLESPACE:
 			if (!pg_tablespace_ownercheck(address.objectId, roleid))
 				aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TABLESPACE,
@@ -2467,19 +2496,10 @@ getObjectDescription(const ObjectAddress *object)
 			}
 
 		case OCLASS_LANGUAGE:
-			{
-				HeapTuple	langTup;
+			appendStringInfo(&buffer, _("language %s"),
+							 get_language_name(object->objectId, false));
+			break;
 
-				langTup = SearchSysCache1(LANGOID,
-										  ObjectIdGetDatum(object->objectId));
-				if (!HeapTupleIsValid(langTup))
-					elog(ERROR, "cache lookup failed for language %u",
-						 object->objectId);
-				appendStringInfo(&buffer, _("language %s"),
-				  NameStr(((Form_pg_language) GETSTRUCT(langTup))->lanname));
-				ReleaseSysCache(langTup);
-				break;
-			}
 		case OCLASS_LARGEOBJECT:
 			appendStringInfo(&buffer, _("large object %u"),
 							 object->objectId);
@@ -2667,6 +2687,27 @@ getObjectDescription(const ObjectAddress *object)
 				break;
 			}
 
+		case OCLASS_TRANSFORM:
+			{
+				HeapTuple	trfTup;
+				Form_pg_transform trfForm;
+
+				trfTup = SearchSysCache1(TRFOID,
+										  ObjectIdGetDatum(object->objectId));
+				if (!HeapTupleIsValid(trfTup))
+					elog(ERROR, "could not find tuple for transform %u",
+						 object->objectId);
+
+				trfForm = (Form_pg_transform) GETSTRUCT(trfTup);
+
+				appendStringInfo(&buffer, _("transform for %s language %s"),
+								 format_type_be(trfForm->trftype),
+								 get_language_name(trfForm->trflang, false));
+
+				ReleaseSysCache(trfTup);
+				break;
+			}
+
 		case OCLASS_TRIGGER:
 			{
 				Relation	trigDesc;
diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c
index df7bdfb03c..5f211dacde 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -545,6 +545,7 @@ AggregateCreate(const char *aggName,
 							 parameterModes,	/* parameterModes */
 							 parameterNames,	/* parameterNames */
 							 parameterDefaults, /* parameterDefaults */
+							 PointerGetDatum(NULL),	/* trftypes */
 							 PointerGetDatum(NULL),		/* proconfig */
 							 1, /* procost */
 							 0);	/* prorows */
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index fd5060aa7c..122982951e 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -23,7 +23,9 @@
 #include "catalog/pg_namespace.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_proc_fn.h"
+#include "catalog/pg_transform.h"
 #include "catalog/pg_type.h"
+#include "commands/defrem.h"
 #include "executor/functions.h"
 #include "funcapi.h"
 #include "mb/pg_wchar.h"
@@ -59,7 +61,7 @@ static bool match_prosrc_to_literal(const char *prosrc, const char *literal,
 /* ----------------------------------------------------------------
  *		ProcedureCreate
  *
- * Note: allParameterTypes, parameterModes, parameterNames, and proconfig
+ * Note: allParameterTypes, parameterModes, parameterNames, trftypes, and proconfig
  * are either arrays of the proper types or NULL.  We declare them Datum,
  * not "ArrayType *", to avoid importing array.h into pg_proc_fn.h.
  * ----------------------------------------------------------------
@@ -86,6 +88,7 @@ ProcedureCreate(const char *procedureName,
 				Datum parameterModes,
 				Datum parameterNames,
 				List *parameterDefaults,
+				Datum trftypes,
 				Datum proconfig,
 				float4 procost,
 				float4 prorows)
@@ -116,6 +119,7 @@ ProcedureCreate(const char *procedureName,
 	ObjectAddress myself,
 				referenced;
 	int			i;
+	Oid			trfid;
 
 	/*
 	 * sanity checks
@@ -360,6 +364,10 @@ ProcedureCreate(const char *procedureName,
 		values[Anum_pg_proc_proargdefaults - 1] = CStringGetTextDatum(nodeToString(parameterDefaults));
 	else
 		nulls[Anum_pg_proc_proargdefaults - 1] = true;
+	if (trftypes != PointerGetDatum(NULL))
+		values[Anum_pg_proc_protrftypes - 1] = trftypes;
+	else
+		nulls[Anum_pg_proc_protrftypes - 1] = true;
 	values[Anum_pg_proc_prosrc - 1] = CStringGetTextDatum(prosrc);
 	if (probin)
 		values[Anum_pg_proc_probin - 1] = CStringGetTextDatum(probin);
@@ -624,6 +632,15 @@ ProcedureCreate(const char *procedureName,
 	referenced.objectSubId = 0;
 	recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
 
+	/* dependency on transform used by return type, if any */
+	if ((trfid = get_transform_oid(returnType, languageObjectId, true)))
+	{
+		referenced.classId = TransformRelationId;
+		referenced.objectId = trfid;
+		referenced.objectSubId = 0;
+		recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+	}
+
 	/* dependency on parameter types */
 	for (i = 0; i < allParamCount; i++)
 	{
@@ -631,6 +648,15 @@ ProcedureCreate(const char *procedureName,
 		referenced.objectId = allParams[i];
 		referenced.objectSubId = 0;
 		recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+
+		/* dependency on transform used by parameter type, if any */
+		if ((trfid = get_transform_oid(allParams[i], languageObjectId, true)))
+		{
+			referenced.classId = TransformRelationId;
+			referenced.objectId = trfid;
+			referenced.objectSubId = 0;
+			recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+		}
 	}
 
 	/* dependency on parameter default expressions */
@@ -1128,3 +1154,21 @@ fail:
 	*newcursorpos = newcp;
 	return false;
 }
+
+List *
+oid_array_to_list(Datum datum)
+{
+	ArrayType *array = DatumGetArrayTypeP(datum);
+	Datum *values;
+	int nelems;
+	int i;
+	List *result = NIL;
+
+	deconstruct_array(array,
+					  OIDOID,
+					  sizeof(Oid), true, 'i',
+					  &values, NULL, &nelems);
+	for (i = 0; i < nelems; i++)
+		result = lappend_oid(result, values[i]);
+	return result;
+}