Hi there,
I've got a rather large PL/pgSQL function which returns a varchar
(though it could be text, char or blob, I'm not fussy) containing JSON
information (where JSON is Javascript Object Notation). The middle
tier of the app does pretty much sweet FA except pass this straight
back to the client. I'm interested in seeing how much faster I can get
the app to process a request this way as opposed to retrieving the
data over three or four calls to the DB before constructing the JSON
response in the middle tier.
I've got to the point where I suspect the concatenation could do with
some attention. What is the fastest way of returning this to the
client?
I thought that storing the individual segments of text in an array and
stitiching it all together at the end of the function may be a fast
way of doing things, using an
array_to_string(resultArray,'');
call. However I have my doubts whether the
resultArray := array_append(resultArray,'next token');
is performant as it looks as though it's constructing a new array from
the argument each time its called. Can someone confirm or rebut this?
How would a simple
result := result || 'next token';
perform? The result size is in the 20-25 Kb range.
A mate mentioned that the way Oracle's OWS does it is to stream the
response back as a blob. I presume he means that the function could
iterate over the different queries' result-sets and simply write the
results to the blob before returning. Can anyone shed any light on
this approach and its applicabilty to PostgreSQL?
Cheers
Michael
