Am trying to fetch data from remote db and insert into three local tables. Currently I have a query which is successfully getting the data into one table.
But now I was thinking of creating a function to do the whole procedure including inserting the data into the three tables. I guess am stuck here…
Best Answer
You could use a data-modifying CTE (introduced with PostgreSQL 9.1) for that to perform well. Consider the following demo:
Call:
Tested with PostgreSQL 9.1.4.
If you put your password here, I would create that function in a separate schema and remove all access from schema and function from the general public.
You obviously have dblink installed, but for the general public: the extension has to be installed once per database: