Pour préparer l'obtention des types de colonnes en une seule demande, nous avons besoin de postgres . Vous pouvez également utiliser l' image finie .Je m'empresse de décevoir tout de suite: l'implémentation ne sera pas en SQL! Bien, plus, et il existe des fonctions appropriées. Mais le code résultant m'a paru trop compliqué et moche, et donc, j'ai tout implémenté sur ... C! Vrai, malheureusement, non pas comme une extension, mais comme une modification de la source.Ici, en fait, le patch lui-même pour ajouter des types de colonnes aux noms (pour les clients versions 2 et 3), avec commentairesdiff --git a/src/backend/access/common/printtup.c b/src/backend/access/common/printtup.c
index 24d6cd0249..6862b69258 100644
@@ -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
@@ -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)
Eh bien, et en bonus, un patch similaire pour ajouter des types de colonnes aux noms lors de la conversion en jsondiff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 26d293709a..fb025adf58 100644
@@ -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))
{
Eh bien, pourquoi ai-je fait tout ça?! Parce que je peux bien que, non seulement, j'ai une idée intéressante de la façon dont il peut être utilisé efficacement, mais jusqu'à présent, mes mains n'ont pas atteint ...Exempleset 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}]