1. Dražen Odobašić
  2. json_91

Commits

Dražen Odobašić  committed 4fe8674

Added query_to_json function, based on pg92 patches

  • Participants
  • Parent commits e9885d2
  • Branches master

Comments (0)

Files changed (3)

File sql/json.sql

View file
 RETURNS json
 AS 'MODULE_PATHNAME','row_to_json_pretty'
 LANGUAGE C STRICT IMMUTABLE;
+
+CREATE FUNCTION query_to_json(text)
+RETURNS json
+AS 'MODULE_PATHNAME','query_to_json'
+LANGUAGE C STRICT IMMUTABLE;
+
+CREATE FUNCTION query_to_json(text, bool)
+RETURNS json
+AS 'MODULE_PATHNAME','query_to_json_pretty'
+LANGUAGE C STRICT IMMUTABLE;

File src/json.c

View file
 	JSON_STACKOP_POP
 } JsonStackOp;
 
+typedef struct
+{
+	char    *colname;
+	Oid      typid;
+	TYPCATEGORY tcategory;
+	Oid      toutputfunc;
+	bool     tisvarlena;
+
+} result_metadata;
+
 static void json_validate_cstring(char *input);
 static void json_lex(JsonLexContext *lex);
 static void json_lex_string(JsonLexContext *lex);
 static char *extract_mb_char(char *s);
 static void composite_to_json(Datum composite, StringInfo result, bool use_line_feeds);
 static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
-							  Datum *vals, bool *nulls, int *valcount, 
-							  TYPCATEGORY tcategory, Oid typoutputfunc, 
+							  Datum *vals, bool *nulls, int *valcount,
+							  TYPCATEGORY tcategory, Oid typoutputfunc,
 							  bool use_line_feeds);
 static void array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds);
-static void  escape_json(StringInfo buf, const char *str);
+static void rowset_row_to_json(int rownum, StringInfo result, result_metadata *meta);
+static void query_to_json_internal(char* query, StringInfo result, bool use_line_feeds);
+static void escape_json(StringInfo buf, const char *str);
 static Oid JSONOID;
 
 PG_FUNCTION_INFO_V1(json_in);
 PG_FUNCTION_INFO_V1(array_to_json_pretty);
 PG_FUNCTION_INFO_V1(row_to_json);
 PG_FUNCTION_INFO_V1(row_to_json_pretty);
+PG_FUNCTION_INFO_V1(query_to_json);
+PG_FUNCTION_INFO_V1(query_to_json_pretty);
 
 /* fake type category for JSON so we can distinguish it in datum_to_json */
 #define TYPCATEGORY_JSON 'j'
 	JSONOID = *my_extra;
 }
 
+static void
+rowset_row_to_json(int rownum, StringInfo result, result_metadata *meta)
+{
+	int			i;
+
+	appendStringInfoChar(result, '{');
+
+	for (i = 1; i <= SPI_tuptable->tupdesc->natts; i++)
+	{
+		Datum		colval;
+		bool		isnull;
+
+		if (i > 1)
+			appendStringInfoChar(result, ',');
+
+		escape_json(result,meta[i-1].colname);
+		appendStringInfoChar(result,':');
+
+		/* XXX should we get a detoasted copy of this? */
+
+		colval = SPI_getbinval(SPI_tuptable->vals[rownum],
+							   SPI_tuptable->tupdesc,
+							   i,
+							   &isnull);
+
+		datum_to_json(colval, isnull, result, meta[i-1].tcategory, meta[i-1].toutputfunc);
+	}
+
+	appendStringInfoChar(result, '}');
+
+
+}
+static void
+query_to_json_internal(char* query, StringInfo result, bool use_line_feeds)
+{
+	//text   *qtext = PG_GETARG_TEXT_PP(0);
+	//bool   lf_between_records = PG_GETARG_BOOL(1);
+	result_metadata   *meta;
+	int     i;
+	char   *sep;
+
+	sep = use_line_feeds ? ",\n " : ",";
+
+	appendStringInfoChar(result,'[');
+
+	SPI_connect();
+
+	if (SPI_execute(query, true, 0) != SPI_OK_SELECT)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATA_EXCEPTION),
+				 errmsg("invalid query")));
+
+	meta = palloc(SPI_tuptable->tupdesc->natts * sizeof(result_metadata));
+
+	for (i = 1; i <= SPI_tuptable->tupdesc->natts; i++)
+	{
+		result_metadata *m = &(meta[i-1]);
+		m->colname = SPI_fname(SPI_tuptable->tupdesc, i);
+		m->typid = SPI_gettypeid(SPI_tuptable->tupdesc, i);
+		/*
+		 * for our purposes RECORDARRAYOID is an ARRAY
+		 * and RECORDOID is a composite
+		 */
+		if (m->typid == RECORDARRAYOID)
+			m->tcategory = TYPCATEGORY_ARRAY;
+		else if (m->typid == RECORDOID)
+			m->tcategory = TYPCATEGORY_COMPOSITE;
+		else
+			m->tcategory = TypeCategory(m->typid);
+		getTypeOutputInfo(m->typid, &(m->toutputfunc), &(m->tisvarlena));
+	}
+
+	for (i = 0; i < SPI_processed; i++)
+	{
+		if (i > 0)
+			appendStringInfoString(result,sep);
+
+		rowset_row_to_json(i, result, meta);
+	}
+
+	SPI_finish();
+
+	appendStringInfoChar(result,']');
+
+}
+
 /*
  * SQL function array_to_json(row)
  */
 };
 
 /*
+ * SQL function query_to_json(row)
+ */
+extern Datum
+query_to_json(PG_FUNCTION_ARGS)
+{
+	text   *qtext = PG_GETARG_TEXT_PP(0);
+	char   *query = text_to_cstring(qtext);
+	StringInfo	result;
+
+	get_json_oid(fcinfo);
+	result = makeStringInfo();
+
+	query_to_json_internal(query, result, false);
+
+	PG_RETURN_TEXT_P(cstring_to_text(result->data));
+};
+
+/*
+ * SQL function query_to_json(row, prettybool)
+ */
+extern Datum
+query_to_json_pretty(PG_FUNCTION_ARGS)
+{
+	text   *qtext = PG_GETARG_TEXT_PP(0);
+	char   *query = text_to_cstring(qtext);
+	bool     use_line_feeds = PG_GETARG_BOOL(1);
+	StringInfo	result;
+
+	get_json_oid(fcinfo);
+	result = makeStringInfo();
+
+	query_to_json_internal(query, result, use_line_feeds);
+
+	PG_RETURN_TEXT_P(cstring_to_text(result->data));
+};
+
+/*
  * Produce a JSON string literal, properly escaping characters in the text.
  */
 static void

File src/json.h

View file
 extern Datum json_out(PG_FUNCTION_ARGS);
 extern Datum json_recv(PG_FUNCTION_ARGS);
 extern Datum json_send(PG_FUNCTION_ARGS);
+extern Datum query_to_json(PG_FUNCTION_ARGS);
+extern Datum query_to_json_pretty(PG_FUNCTION_ARGS);
 extern Datum array_to_json(PG_FUNCTION_ARGS);
 extern Datum array_to_json_pretty(PG_FUNCTION_ARGS);
 extern Datum row_to_json(PG_FUNCTION_ARGS);