Get best of the week

Resep PostgreSQL: Dapatkan Jenis Kolom dalam Satu Permintaan

Untuk mempersiapkan mendapatkan jenis kolom dalam satu permintaan, kami membutuhkan postgres . Anda juga dapat menggunakan gambar yang sudah jadi .

Saya segera mengecewakan: implementasi tidak akan di SQL! Meskipun, lebih, dan ada fungsi yang sesuai. Tetapi kode yang dihasilkan bagi saya tampak terlalu rumit dan jelek, dan oleh karena itu, saya menerapkan semuanya pada ... C! Benar, sayangnya, bukan sebagai ekstensi, tetapi sebagai modifikasi sumber.

Di sini, pada kenyataannya, tambalan itu sendiri untuk menambahkan jenis kolom ke nama (untuk klien versi 2 dan 3), dengan komentar

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)

Nah, dan bonus, tambalan serupa untuk menambahkan jenis kolom ke nama saat mengkonversi ke 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))
 	{

Nah, mengapa saya melakukan semua ini ?! Karena saya bisa walaupun, tidak hanya, saya punya satu ide menarik bagaimana itu dapat digunakan secara efektif, tetapi sejauh ini tangan saya belum mencapai ...

Contoh
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