Рецепты PostgreSQL: получение типов колонок за один запрос

Для приготовления получения типов колонок за один запрос нам понадобится postgres. Можно также воспользоваться готовым образом.

Сразу спешу огорчить: реализация будет не на SQL! Хотя, кончено, и там есть подходящие функции. Но мне показался слишком сложным и некрасивым получающийся код, и поэтому, я всё реализовал на… C! Правда, к сожалению, не как расширение, а как модификация исходников.

Вот, собственно говоря, сам патч для добавления типов колонок к именам (для клиентов версий 2 и 3), с комментариями

diff --git a/src/backend/access/common/printtup.c b/src/backend/access/common/printtup.c
index 24d6cd0249..6862b69258 100644
--- a/src/backend/access/common/printtup.c
+++ b/src/backend/access/common/printtup.c
@@ -22,6 +22,9 @@
 #include "utils/lsyscache.h"
 #include "utils/memdebug.h"
 #include "utils/memutils.h"
+#include "utils/guc.h"        // подключаем
+#include "utils/syscache.h"   // нужные
+#include "catalog/pg_type.h"  // заголовки
 
 
 static void printtup_startup(DestReceiver *self, int operation,
@@ -70,6 +73,8 @@ typedef struct
 	MemoryContext tmpcontext;	/* Memory context for per-row workspace */
 } DR_printtup;
 
+static bool append; // добавлять типы к именам колонок?
+
 /* ----------------
  *		Initialize: create a DestReceiver for printtup
  * ----------------
@@ -132,6 +137,7 @@ printtup_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
 {
 	DR_printtup *myState = (DR_printtup *) self;
 	Portal		portal = myState->portal;
+	append = GetConfigOption("config.append_type_to_column_name", true, true) != NULL; // хочет ли пользователь базы добавлять типы к именам колонок?
 
 	/*
 	 * Create I/O buffer to be used for all messages.  This cannot be inside
@@ -236,6 +242,7 @@ SendRowDescriptionCols_3(StringInfo buf, TupleDesc typeinfo, List *targetlist, i
 	 * character set overhead.
 	 */
 	enlargeStringInfo(buf, (NAMEDATALEN * MAX_CONVERSION_GROWTH /* attname */
+							+ (append ? NAMEDATALEN * MAX_CONVERSION_GROWTH + sizeof("::") - 1 : 0) // если нужно добавлять типы к именам колонок, то добавим ещё места для их хранения
 							+ sizeof(Oid)	/* resorigtbl */
 							+ sizeof(AttrNumber)	/* resorigcol */
 							+ sizeof(Oid)	/* atttypid */
@@ -283,6 +290,17 @@ SendRowDescriptionCols_3(StringInfo buf, TupleDesc typeinfo, List *targetlist, i
 		else
 			format = 0;
 
+		if (append) { // если нужно добавлять типы к именам колонок, то
+			HeapTuple typeTuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(att->atttypid)); // поищем тип колонки в кэше
+			StringInfoData buf_; // временный буфер для хранения
+			initStringInfo(&buf_); // иниализируем его
+			appendStringInfo(&buf_, "%s::", NameStr(att->attname)); // запишем в буфер сначала название колонки, потом два двоеточия
+			if (HeapTupleIsValid(typeTuple)) appendStringInfoString(&buf_, NameStr(((Form_pg_type) GETSTRUCT(typeTuple))->typname)); // если нашли тип колонки, то запишем его в буфер
+			else appendStringInfo(&buf_, "%i", att->atttypid); // иначе в буфер запишем oid
+			pq_writestring(buf, buf_.data); // отправим данные клиенту
+			ReleaseSysCache(typeTuple); // разблокируем кэш
+			pfree(buf_.data); // очистим временный буфер
+		} else // ну а если не надо добавлять типы к именам колонок, то всё остаётся по-старому
 		pq_writestring(buf, NameStr(att->attname));
 		pq_writeint32(buf, resorigtbl);
 		pq_writeint16(buf, resorigcol);
@@ -311,6 +329,17 @@ SendRowDescriptionCols_2(StringInfo buf, TupleDesc typeinfo, List *targetlist, i
 		/* If column is a domain, send the base type and typmod instead */
 		atttypid = getBaseTypeAndTypmod(atttypid, &atttypmod);
 
+		if (append) { // если нужно добавлять типы к именам колонок, то
+			HeapTuple typeTuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(att->atttypid)); // поищем тип колонки в кэше
+			StringInfoData buf_; // временный буфер для хранения
+			initStringInfo(&buf_); // иниализируем его
+			appendStringInfo(&buf_, "%s::", NameStr(att->attname)); // запишем в буфер сначала название колонки, потом два двоеточия
+			if (HeapTupleIsValid(typeTuple)) appendStringInfoString(&buf_, NameStr(((Form_pg_type) GETSTRUCT(typeTuple))->typname)); // если нашли тип колонки, то запишем его в буфер
+			else appendStringInfo(&buf_, "%i", att->atttypid); // иначе в буфер запишем oid
+			pq_sendstring(buf, buf_.data); // отправим данные клиенту
+			ReleaseSysCache(typeTuple); // разблокируем кэш
+			pfree(buf_.data); // очистим временный буфер
+		} else // ну а если не надо добавлять типы к именам колонок, то всё остаётся по-старому
 		pq_sendstring(buf, NameStr(att->attname));
 		/* column ID only info appears in protocol 3.0 and up */
 		pq_sendint32(buf, atttypid);
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 6c2db93573..f23de7f788 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -992,6 +992,8 @@ exec_simple_query(const char *query_string)
 	bool		use_implicit_block;
 	char		msec_str[32];
 
+	SetConfigOption("config.append_type_to_column_name", NULL, PGC_USERSET, PGC_S_SESSION); // перед каждым выполнением SQL-кода зануляем нашу переменную, чтобы пользователь мог сам решить, нужны ли ему типы колонок
+
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1959,6 +1961,8 @@ exec_execute_message(const char *portal_name, long max_rows)
 	bool		was_logged = false;
 	char		msec_str[32];
 
+	SetConfigOption("config.append_type_to_column_name", NULL, PGC_USERSET, PGC_S_SESSION); // перед каждым выполнением заранее подготовленного SQL-кода зануляем нашу переменную, чтобы пользователь мог сам решить, нужны ли ему типы колонок
+
 	/* Adjust destination to tell printtup.c what to do */
 	dest = whereToSendOutput;
 	if (dest == DestRemote)

Ну и бонусом, аналогичный патч для добавления типов колонок к именам при преобразованиях в json

diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 26d293709a..fb025adf58 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -32,6 +32,7 @@
 #include "utils/jsonapi.h"
 #include "utils/typcache.h"
 #include "utils/syscache.h"
+#include "utils/guc.h" // подключаем нужный заголовок
 
 /*
  * The context of the parser is maintained by the recursive descent
@@ -106,6 +107,8 @@ static void add_json(Datum val, bool is_null, StringInfo result,
 					 Oid val_type, bool key_scalar);
 static text *catenate_stringinfo_string(StringInfo buffer, const char *addon);
 
+static bool append; // добавлять типы к именам колонок?
+
 /* the null action object used for pure validation */
 static JsonSemAction nullSemAction =
 {
@@ -1789,6 +1792,17 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
 		needsep = true;
 
 		attname = NameStr(att->attname);
+		if (append) { // если нужно добавлять типы к именам колонок, то
+			HeapTuple typeTuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(att->atttypid)); // поищем тип колонки в кэше
+			StringInfoData buf; // временный буфер для хранения
+			initStringInfo(&buf); // иниализируем его
+			appendStringInfo(&buf, "%s::", attname); // запишем в буфер сначала название колонки, потом два двоеточия
+			if (HeapTupleIsValid(typeTuple)) appendStringInfoString(&buf, NameStr(((Form_pg_type) GETSTRUCT(typeTuple))->typname)); // если нашли тип колонки, то запишем его в буфер
+			else appendStringInfo(&buf, "%i", att->atttypid); // иначе в буфер запишем oid
+			escape_json(result, buf.data); // записываем результат
+			ReleaseSysCache(typeTuple); // разблокируем кэш
+			pfree(buf.data); // очистим временный буфер
+		} else // ну а если не надо добавлять типы к именам колонок, то всё остаётся по-старому
 		escape_json(result, attname);
 		appendStringInfoChar(result, ':');
 
@@ -1848,6 +1862,7 @@ array_to_json(PG_FUNCTION_ARGS)
 {
 	Datum		array = PG_GETARG_DATUM(0);
 	StringInfo	result;
+	append = GetConfigOption("config.append_type_to_column_name", true, true) != NULL; // хочет ли пользователь базы добавлять типы к именам колонок?
 
 	result = makeStringInfo();
 
@@ -1865,6 +1880,7 @@ array_to_json_pretty(PG_FUNCTION_ARGS)
 	Datum		array = PG_GETARG_DATUM(0);
 	bool		use_line_feeds = PG_GETARG_BOOL(1);
 	StringInfo	result;
+	append = GetConfigOption("config.append_type_to_column_name", true, true) != NULL; // хочет ли пользователь базы добавлять типы к именам колонок?
 
 	result = makeStringInfo();
 
@@ -1881,6 +1897,7 @@ row_to_json(PG_FUNCTION_ARGS)
 {
 	Datum		array = PG_GETARG_DATUM(0);
 	StringInfo	result;
+	append = GetConfigOption("config.append_type_to_column_name", true, true) != NULL; // хочет ли пользователь базы добавлять типы к именам колонок?
 
 	result = makeStringInfo();
 
@@ -1898,6 +1915,7 @@ row_to_json_pretty(PG_FUNCTION_ARGS)
 	Datum		array = PG_GETARG_DATUM(0);
 	bool		use_line_feeds = PG_GETARG_BOOL(1);
 	StringInfo	result;
+	append = GetConfigOption("config.append_type_to_column_name", true, true) != NULL; // хочет ли пользователь базы добавлять типы к именам колонок?
 
 	result = makeStringInfo();
 
@@ -1917,6 +1935,7 @@ to_json(PG_FUNCTION_ARGS)
 	StringInfo	result;
 	JsonTypeCategory tcategory;
 	Oid			outfuncoid;
+	append = GetConfigOption("config.append_type_to_column_name", true, true) != NULL; // хочет ли пользователь базы добавлять типы к именам колонок?
 
 	if (val_type == InvalidOid)
 		ereport(ERROR,
@@ -1945,6 +1964,7 @@ json_agg_transfn(PG_FUNCTION_ARGS)
 				oldcontext;
 	JsonAggState *state;
 	Datum		val;
+	append = GetConfigOption("config.append_type_to_column_name", true, true) != NULL; // хочет ли пользователь базы добавлять типы к именам колонок?
 
 	if (!AggCheckCallContext(fcinfo, &aggcontext))
 	{
@@ -2046,6 +2066,7 @@ json_object_agg_transfn(PG_FUNCTION_ARGS)
 				oldcontext;
 	JsonAggState *state;
 	Datum		arg;
+	append = GetConfigOption("config.append_type_to_column_name", true, true) != NULL; // хочет ли пользователь базы добавлять типы к именам колонок?
 
 	if (!AggCheckCallContext(fcinfo, &aggcontext))
 	{

Ну и зачем я всё это сделал?! Потому, что могу хотя, не только, есть у меня одна интересная идея, как это можно эффективно использовать, но пока руки не дошли…

Пример

set config.append_type_to_column_name = true;
with s as (
    select * from task
) select json_agg(s) from s

[{"id::int8":1,"parent::int8":null,"dt::timestamptz":"2020-05-20T07:53:03.952542+05:00","start::timestamptz":"2020-05-20T07:53:04.930703+05:00","stop::timestamptz":"2020-05-20T07:53:04.976998+05:00","group::text":"group","max::int4":null,"pid::int4":5905,"request::text":"select now()","response::text":"2020-05-20 07:53:04.976151+05","state::state":"DONE","timeout::interval":null,"delete::bool":false,"repeat::interval":null,"drift::bool":true,"count::int4":null,"live::interval":null,"remote::text":null,"append::bool":false,"header::bool":true,"string::bool":true,"null::text":"\\N","delimiter::char":"\t","quote::char":null,"escape::char":null},  {"id::int8":2,"parent::int8":null,"dt::timestamptz":"2020-05-20T07:53:12.780313+05:00","start::timestamptz":"2020-05-20T07:53:13.299168+05:00","stop::timestamptz":"2020-05-20T07:53:13.352304+05:00","group::text":"group","max::int4":null,"pid::int4":5908,"request::text":"select 1","response::text":"1","state::state":"DONE","timeout::interval":null,"delete::bool":false,"repeat::interval":null,"drift::bool":true,"count::int4":null,"live::interval":null,"remote::text":null,"append::bool":false,"header::bool":true,"string::bool":true,"null::text":"\\N","delimiter::char":"\t","quote::char":null,"escape::char":null},  {"id::int8":3,"parent::int8":null,"dt::timestamptz":"2020-05-20T07:53:15.954607+05:00","start::timestamptz":"2020-05-20T07:53:16.477578+05:00","stop::timestamptz":"2020-05-20T07:53:16.536582+05:00","group::text":"group","max::int4":null,"pid::int4":5910,"request::text":"select 1/0","response::text":"elevel\t20\noutput_to_server\ttrue\nfilename\tint.c\nlineno\t822\nfuncname\tint4div\ndomain\tpostgres-12\ncontext_domain\tpostgres-12\nsqlerrcode\t33816706\nmessage\tdivision by zero\nmessage_id\tdivision by zero\nROLLBACK","state::state":"FAIL","timeout::interval":null,"delete::bool":false,"repeat::interval":null,"drift::bool":true,"count::int4":null,"live::interval":null,"remote::text":null,"append::bool":false,"header::bool":true,"string::bool":true,"null::text":"\\N","delimiter::char":"\t","quote::char":null,"escape::char":null}]