Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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);
share|improve this answer
    
+1 for teaching me ilike :) –  FreshPhilOfSO Jul 28 '12 at 18:32
    
@Phil: A rather unexpected collateral benefit. :) If you like ILIKE, you may be interested in this related answer. –  Erwin Brandstetter Jul 28 '12 at 18:59
    
I'm an Oracle guy & would use REGEXP_LIKE(). It's good knowing equivalents :) –  FreshPhilOfSO Jul 28 '12 at 19:04
    
I wanted to include another condition after WHERE remote_column2 ilike ''' || uniq_id || '%''' FROM some_local_table i.e and row_counter>'||(select coalesce(max(row_counter),-1) from yet_another_local_table how would I go about that? –  G. Ngotho Jul 28 '12 at 19:44
    
@user1328181: added to my answer to cover your 2nd question. –  Erwin Brandstetter Jul 28 '12 at 21:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.