DBLINK match a value from a local table

Question

Am trying to run the following dblink start transaction;

select dblink_connect('host=remote port=5432 dbname=remote_db user=user password=pass');
insert into local_table (column1, column2 )
select * from dblink(`
    select remote_column1 ltrim(remote_column2, ''TEST'') from remote_table
       where remote_column2 ilike ''TEST%''  
) as t(column1 varchar, column2 varchar) ;

now the above works fine, but am encountering a challenge:- I want instead of hardcoding TEST, I want it to be queried from a local table so I would wish to substitute TEST to something like SELECT uniq_id from some_local_table The logic behind it is I want to get records which has prefix matching the result of SELECT uniq_id from some_local_table but trim the prefix when inserting to local_table hope someone understands me

Erwin Brandstetter · Accepted Answer · 2013-10-29T22:54:49.393

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 > ' || coalesce((
                SELECT max(row_counter)
                FROM   yet_another_local_table), -1)
          FROM   some_local_table
          --WHERE <some_condition>
          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 > %3s'
            ,uniq_id,  uniq_id || '%'
            ,COALESCE((
               SELECT max(row_counter)
               FROM   yet_another_local_table), -1)
            )
          FROM   some_local_table
          --WHERE <some_condition>
          LIMIT  1)
   ) AS t(column1 varchar, column2 varchar);

DBLINK match a value from a local table

1 Answers1