sending records from one function to other with dblink

Hello,

I currently have two functions, one on local side and one on remote.

The local function creates the dblink with all the required parameters, then using dblink() function I call the remote function.

For the parameters of remote function I want to pass an array of records, I create this array using ARRAY(select row(a,b,c,d) from x) function. I created the query to be executed on the remote site using FORMAT() function.

Every thing is fine till here. Now on the remote side I wished the argument type of the function could be array of records but postgres doesn’t allow RECORD[] to be a parameter for PlPgSQL function.

Anyways, what I want the remote function to do is that accept this array of records and then create a temporary table out of those records, so that I can perform some join on that table. And eventually send some other records as result.

I am stuck at the remote function accepting RECORD[].  I tried creating a single TYPE on both databases. That didn’t work. Tried to use two dimensional text array and then tried unnesting it using UNNEST(). It casted array to just text.

What I want to do:

·         I have a table with four columns on LOCAL db. Which I want to send to REMOTE db using dblink.

·         On REMOTE db I will do join of this passed table with some other remote tables.

·         Then create a resultant temporary table and that will be sent back to LOCAL side.

I hope I am clear with my intensions.

Any help will be appreciated.   

Thanks and Regards,

Piyush Jaisingkar ,

App. Software Dev. Sr. App. Conslt. | NTT DATA GDS, Pune.

NTT DATA Global Delivery Services Private Limited Consulting | Digital | Managed Services | Industry Solutions

Learn more: