Merging query results from multiple databases

mstaniloiu

Hello,<br />
<br />
I have to create some reports which should create their data set by merging data from multiple (identical) databases. The number of databases is not known in advice (in theory it should be unlimited).<br />
I was thinking of using dblink in a way similar to this:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT *
FROM dblink('hostaddr=<ipaddr1> dbname=<dbname>', 'select userid, jobid from view1.jobs')
AS t1(userid bigint, jobid text)
UNION ALL
SELECT *
FROM dblink('hostaddr=<ipaddr2> dbname=<dbname>', 'select userid, jobid from view1.jobs')
AS t1(userid bigint, jobid text)</pre>
<br />
The databases have to be specified by their IP (they all have the same name, and the port is also the same). I am not allowed to use a multiple selection list for specifying the IP addresses, so I am thinking of using a text box (the IP addresses will be separated by commas).<br />
<br />
However, I don't even know where to start when it comes to writing a corresponding SQL query (I think I should somehow split the string containing the IP addresses and then somehow iterate though the results). Can you give me some clues on how to do this?

mwilliams

In the beforeOpen of your dataSet, you would take the comma separated string that was entered for the parameter and run split(",") on it, which will create a string array for you to step through. From here, you can build your queryText while stepping through the array. You can set the dataSet's query text with:

this.queryText = "select blah from blah";

So, your loop that steps through your array creating each individual query that is to be unioned should allow for this to be dynamic so it does not matter how many values are entered into the parameter.

Hope this helps. Let me know if I misunderstood something or if you have more questions.