Get best of the week

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}]

More articles:


All Articles