DBLINK match a value from a local table
This should actually work with a correlated subquery:

SELECT dblink_connect(
                'host=remote port=5432 dbname=remote_db user=user password=pass');


INSERT INTO local_table (column1, column2)
SELECT *
FROM   dblink((
          SELECT '
             SELECT remote_column1 ltrim(remote_column2, ''' || uniq_id || ''')
             FROM   remote_table
             WHERE  remote_column2 ILIKE ''' || uniq_id || '%''
             AND    row_counter &gt; ' || coalesce((
                SELECT max(row_counter)
                FROM   yet_another_local_table), -1)
          FROM   some_local_table
          --WHERE &lt;some_condition&gt;
          LIMIT  1  -- make sure it's one row
          )
       ) AS t(column1 varchar, column2 varchar);

SELECT dblink_disconnect();


The meta-levels and layers of single-quotes may be a bit confusing.

This form is vulnerable to SQLi. Make sure nothing bad can come from some_local_table or use quote_literal(uniq_id) in place of plain uniq_id.



In reply to the comment I added a nested correlated subquery to the example. It returns a single row, so it should work fine. Could also be done by left-joining to it in the FROM clause.

Note that I rewrote the expression to make COALESCE() the outer wrapper, so the query won't fail if there are no rows yet_another_local_table. The way you had it would only catch the case where max(row_counter) IS NULL.

Probably easier to read with format() (requires Postgres 9.1+):

INSERT INTO local_table (column1, column2)
SELECT *
FROM   dblink(
         (SELECT format(
            'SELECT remote_column1 ltrim(remote_column2, %1$L)
             FROM   remote_table
             WHERE  remote_column2 ILIKE %2$L
             AND    row_counter &gt; %3s'
            ,uniq_id,  uniq_id || '%'
            ,COALESCE((
               SELECT max(row_counter)
               FROM   yet_another_local_table), -1)
            )
          FROM   some_local_table
          --WHERE &lt;some_condition&gt;
          LIMIT  1)
   ) AS t(column1 varchar, column2 varchar);