As mentioned here postgres_fdw has no access to indexes.
A workaround is to create an view on remote server and then create foreign table wrapper to this view on local one.
But what if I want to pass a parameter to my view? Normally I would create a function(myparam) that RETURNS TABLE(). But how call it via postgres_fdw?
Any ideas to solve this situation (prefer to not use dblink if it is not needed)?
EXAMPLE
I have query like this to execute on my remote database:
select count(f.id_foo)
from foo f
where f.date < _my_date
As you can see there is an param _my_date inside.
So I have created foreign table my_remote_server_public.my_remote_server_public_foo and run it from local database like:
select count(f.id_foo)
from my_remote_server_public.my_remote_server_public_foo f
where f.date < _my_date
But when I do this - it lasts 2-3 minutes becouse postgres_fdw has no access to foo indexes.
I thought about creating a function get_foo_by_date(_my_date date) on a remote database and call it via postgres_fdw from the local one but dont know is it even possible...
UPDATE
Let's assume I will handle a normal view as a foreign table with constant date inside.
This view will return me an list of IDs from remote table.
I want to delete listed rows from remote table and archive these into local one.
When I call it like:
EXECUTE
'WITH rows_to_delete
AS (DELETE from my_remote_server_public_foo
WHERE id_foo
IN
(SELECT * FROM my_remote_server_public_view_of_rows_to_delete) RETURNING *)
INSERT INTO my_local_table
SELECT * FROM rows_to_delete';
It lasts 5 min... Again becouse DELETE query have no access to indexes... Do I need to use dblink calling a function here too? Any other workaround?
