v25-0006-Support-CREATE-TRANSFORM-and-DROP-TRANSFORM-commands.patch

From dd6a9720ac3bd2e4cac1f071602a39f1039ac28e Mon Sep 17 00:00:00 2001
From: "Zheng (Zane) Li" <zhelli@amazon.com>
Date: Thu, 6 Oct 2022 15:41:15 +0000
Subject: [PATCH 6/6] Support CREATE TRANSFORM and DROP TRANSFORM commands.

Removed an undesirable 'on' from the identity string for TRANSFORM
in getObjectIdentityParts. This is needed to make deparse of DROP
TRANSFORM command work since 'on' is not present in the current
DROP TRANSFORM syntax. For example, the correct syntax is
drop transform trf for int language sql;
instead of
drop transform trf for int on language sql;
---
 src/backend/catalog/objectaddress.c          |   2 +-
 src/backend/commands/ddl_deparse.c           | 154 +++++++++++++++++++
 src/backend/commands/event_trigger.c         |   3 +-
 src/backend/commands/publicationcmds.c       |   2 +
 src/test/regress/expected/object_address.out |   2 +-
 5 files changed, 160 insertions(+), 3 deletions(-)

diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 284ca55469..084acebbe2 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -6001,7 +6001,7 @@ getObjectIdentityParts(const ObjectAddress *object,
 				transformType = format_type_be_qualified(transform->trftype);
 				transformLang = get_language_name(transform->trflang, false);
 
-				appendStringInfo(&buffer, "for %s on language %s",
+				appendStringInfo(&buffer, "for %s language %s",
 								 transformType,
 								 transformLang);
 				if (objname)
diff --git a/src/backend/commands/ddl_deparse.c b/src/backend/commands/ddl_deparse.c
index 5bfa800fc2..5a63bfafac 100755
--- a/src/backend/commands/ddl_deparse.c
+++ b/src/backend/commands/ddl_deparse.c
@@ -54,6 +54,7 @@
 #include "catalog/pg_range.h"
 #include "catalog/pg_rewrite.h"
 #include "catalog/pg_sequence.h"
+#include "catalog/pg_transform.h"
 #include "catalog/pg_ts_config.h"
 #include "catalog/pg_ts_dict.h"
 #include "catalog/pg_ts_parser.h"
@@ -7638,6 +7639,155 @@ deparse_CreateTableAsStmt(CollectedCommand *cmd)
 	return deparse_CreateStmt(objectId, parsetree);
 }
 
+/*
+ * Deparse a CreateTransformStmt (CREATE TRANSFORM).
+ *
+ * Given a transform OID and the parsetree that created it, return an ObjTree
+ * representing the CREATE TRANSFORM command.
+ */
+static ObjTree *
+deparse_CreateTransformStmt(Oid objectId, Node *parsetree)
+{
+	CreateTransformStmt *node = (CreateTransformStmt *) parsetree;
+	ObjTree	*createTransform;
+	ObjTree *sign;
+	HeapTuple trfTup;
+	HeapTuple langTup;
+	HeapTuple procTup;
+	Form_pg_transform trfForm;
+	Form_pg_language langForm;
+	Form_pg_proc procForm;
+	int i;
+
+	/* Get the pg_transform tuple */
+	trfTup = SearchSysCache1(TRFOID, ObjectIdGetDatum(objectId));
+	if (!HeapTupleIsValid(trfTup))
+		elog(ERROR, "cache lookup failure for transform with OID %u",
+			 objectId);
+	trfForm = (Form_pg_transform) GETSTRUCT(trfTup);
+
+	/* Get the corresponding pg_language tuple */
+	langTup = SearchSysCache1(LANGOID, trfForm->trflang);
+	if (!HeapTupleIsValid(langTup))
+		elog(ERROR, "cache lookup failure for language with OID %u",
+			 trfForm->trflang);
+	langForm = (Form_pg_language) GETSTRUCT(langTup);
+
+	/*
+	 * Verbose syntax
+	 *
+	 * CREATE %{or_replace}s TRANSFORM FOR %{typename}D LANGUAGE %{language}I
+	 * ( FROM SQL WITH FUNCTION %{signature}s, TO SQL WITH FUNCTION %{signature_tof}s )
+	 */
+	createTransform = new_objtree("CREATE");
+
+	append_string_object(createTransform, "%{or_replace}s",
+						 node->replace ? "OR REPLACE" : "");
+	append_object_object(createTransform, "TRANSFORM FOR %{typename}D",
+						 new_objtree_for_qualname_id(TypeRelationId,
+													 trfForm->trftype));
+	append_string_object(createTransform, "LANGUAGE %{language}I",
+						 NameStr(langForm->lanname));
+
+	/* deparse the transform_element_list */
+	if (trfForm->trffromsql != InvalidOid)
+	{
+		List *params = NIL;
+
+		/*
+		 * Verbose syntax
+		 *
+		 * CREATE %{or_replace}s TRANSFORM FOR %{typename}D LANGUAGE %{language}I
+		 * ( FROM SQL WITH FUNCTION %{signature}s )
+		 */
+
+		/* Get the pg_proc tuple for the FROM FUNCTION */
+		procTup = SearchSysCache1(PROCOID, trfForm->trffromsql);
+		if (!HeapTupleIsValid(procTup))
+			elog(ERROR, "cache lookup failure for function with OID %u",
+				trfForm->trffromsql);
+		procForm = (Form_pg_proc) GETSTRUCT(procTup);
+
+		/*
+		 * CREATE TRANSFORM does not change function signature so we can use catalog
+		 * to get input type Oids.
+		 */
+		for (i = 0; i < procForm->pronargs; i++)
+		{
+			ObjTree *paramobj = new_objtree("");
+
+			append_object_object(paramobj, "%{type}T",
+								 new_objtree_for_type(procForm->proargtypes.values[i], -1));
+			params = lappend(params, new_object_object(paramobj));
+		}
+
+		sign = new_objtree("");
+
+		append_object_object(sign, "%{identity}D",
+							 new_objtree_for_qualname(procForm->pronamespace,
+													  NameStr(procForm->proname)));
+		append_array_object(sign, "(%{arguments:, }s)", params);
+
+		append_object_object(createTransform, "(FROM SQL WITH FUNCTION %{signature}s", sign);
+		ReleaseSysCache(procTup);
+	}
+	if (trfForm->trftosql != InvalidOid)
+	{
+		List *params = NIL;
+
+		/*
+		 * Verbose syntax
+		 *
+		 * CREATE %{or_replace}s TRANSFORM FOR %{typename}D LANGUAGE %{language}I
+		 * ( FROM SQL WITH FUNCTION %{signature}s, TO SQL WITH FUNCTION %{signature_tof}s )
+		 *
+		 * OR
+		 *
+		 * CREATE %{or_replace}s TRANSFORM FOR %{typename}D LANGUAGE %{language}I
+		 * ( TO SQL WITH FUNCTION %{signature_tof}s )
+		 */
+
+		/* Append a ',' if trffromsql is present, else append '(' */
+		append_string_object(createTransform, "%{comma}s",
+							 trfForm->trffromsql != InvalidOid ? "," : "(");
+
+		/* Get the pg_proc tuple for the TO FUNCTION */
+		procTup = SearchSysCache1(PROCOID, trfForm->trftosql);
+		if (!HeapTupleIsValid(procTup))
+			elog(ERROR, "cache lookup failure for function with OID %u",
+				trfForm->trftosql);
+		procForm = (Form_pg_proc) GETSTRUCT(procTup);
+
+		/*
+		 * CREATE TRANSFORM does not change function signature so we can use catalog
+		 * to get input type Oids.
+		 */
+		for (i = 0; i < procForm->pronargs; i++)
+		{
+			ObjTree *paramobj = new_objtree("");
+
+			append_object_object(paramobj, "%{type}T",
+								 new_objtree_for_type(procForm->proargtypes.values[i], -1));
+			params = lappend(params, new_object_object(paramobj));
+		}
+
+		sign = new_objtree("");
+
+		append_object_object(sign, "%{identity}D",
+							new_objtree_for_qualname(procForm->pronamespace,
+													 NameStr(procForm->proname)));
+		append_array_object(sign, "(%{arguments:, }s)", params);
+
+		append_object_object(createTransform, "TO SQL WITH FUNCTION %{signature_tof}s", sign);
+		ReleaseSysCache(procTup);
+	}
+	append_string_object(createTransform, "%{close_bracket}s", ")");
+
+	ReleaseSysCache(langTup);
+	ReleaseSysCache(trfTup);
+	return createTransform;
+}
+
 
 /*
  * Handle deparsing of simple commands.
@@ -7804,6 +7954,10 @@ deparse_simple_command(CollectedCommand *cmd)
 			command = deparse_AlterTSDictionaryStmt(objectId, parsetree);
 			break;
 
+		case T_CreateTransformStmt:
+			command = deparse_CreateTransformStmt(objectId, parsetree);
+			break;
+
 		default:
 			command = NULL;
 			elog(LOG, "unrecognized node type in deparse command: %d",
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 81edbcb467..25558aa1c5 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -2559,7 +2559,8 @@ publication_deparse_ddl_command_end(PG_FUNCTION_ARGS)
 				 strcmp(obj->objecttype, "text search configuration") == 0 ||
 				 strcmp(obj->objecttype, "text search dictionary") == 0 ||
 				 strcmp(obj->objecttype, "text search parser") == 0 ||
-				 strcmp(obj->objecttype, "text search template") == 0)
+				 strcmp(obj->objecttype, "text search template") == 0 ||
+				 strcmp(obj->objecttype, "transform") == 0)
 			cmdtype = DCT_ObjectDrop;
 		else
 			continue;
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 37d336e7aa..b600550be1 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -927,6 +927,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
 		CommandTag	rewrite_commands[] = {CMDTAG_ALTER_TABLE};
 
 		CommandTag	end_commands[] = {
+			CMDTAG_CREATE_TRANSFORM,
+			CMDTAG_DROP_TRANSFORM,
 			CMDTAG_CREATE_FOREIGN_DATA_WRAPPER,
 			CMDTAG_ALTER_FOREIGN_DATA_WRAPPER,
 			CMDTAG_DROP_FOREIGN_DATA_WRAPPER,
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 3549b63a79..3c4cb7b336 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -490,7 +490,7 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
  policy                    |            |                   | genpol on addr_nsp.gentable                                          | t
  statistics object         | addr_nsp   | gentable_stat     | addr_nsp.gentable_stat                                               | t
  collation                 | pg_catalog | "default"         | pg_catalog."default"                                                 | t
- transform                 |            |                   | for integer on language sql                                          | t
+ transform                 |            |                   | for integer language sql                                             | t
  text search dictionary    | addr_nsp   | addr_ts_dict      | addr_nsp.addr_ts_dict                                                | t
  text search parser        | addr_nsp   | addr_ts_prs       | addr_nsp.addr_ts_prs                                                 | t
  text search configuration | addr_nsp   | addr_ts_conf      | addr_nsp.addr_ts_conf                                                | t
-- 
2.37.1