Remote query execute with postgres_fdw, using an indexes
<p>As mentioned <a href="https://stackoverflow.com/a/33878255/3804906">here</a> <code>postgres_fdw</code> has no access to indexes. </p>

<p>A workaround is to create an view on remote server and then create foreign table wrapper to this view on local one.</p>

<p>But what if I want to pass a parameter to my view? Normally I would create a <code>function(myparam)</code> that <code>RETURNS TABLE()</code>. But how call it via <code>postgres_fdw</code>? </p>

<p>Any ideas to solve this situation (prefer to not use <code>dblink</code> if it is not needed)?</p>

<p><strong>EXAMPLE</strong></p>

<p>I have query like this to execute on my remote database:</p>

<pre><code>select count(f.id_foo)
from foo f 
where f.date &lt; _my_date
</code></pre>

<p>As you can see there is an param <code>_my_date</code> inside.</p>

<p>So I have created foreign table <code>my_remote_server_public.my_remote_server_public_foo</code> and run it from local database like:</p>

<pre><code>select count(f.id_foo)
from my_remote_server_public.my_remote_server_public_foo f 
where f.date &lt; _my_date
</code></pre>

<p>But when I do this - it lasts 2-3 minutes becouse <code>postgres_fdw</code> has no access to <code>foo</code> indexes.</p>

<p>I thought about creating a function <code>get_foo_by_date(_my_date date)</code> on a remote database and call it via <code>postgres_fdw</code> from the local one but dont know is it even possible...</p>

<p><strong>UPDATE</strong> </p>

<p>Let's assume I will handle a normal view as a foreign table with constant date inside. </p>

<p>This view will return me an list of IDs from remote table.</p>

<p>I want to delete listed rows from remote table and archive these into local one.</p>

<p>When I call it like:</p>

<pre><code>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';
</code></pre>

<p>It lasts 5 min... Again becouse <code>DELETE</code> query have no access to indexes... Do I need to use <code>dblink</code> calling a function here too? Any other workaround?</p>