How to pass the variable on postgres using dbblink

Asked

Viewed 202 times

0

Oops, good morning!

I have the following query in postgres:

declare $1 VARCHAR;
    BEGIN
         IF 
        (SELECT COUNT(*) FROM tempo2
            WHERE looping IS NULL) > 0
            THEN
           $1 = (SELECT banco FROM tempo2 WHERE looping IS NULL LIMIT 1);
INSERT INTO...
       SELECT * FROM dblink ('host=xx.xx.xx.xx dbname='||$1||' 
                                 user=xxxxpassword=xxxxx',
        'SELECT ...

My problem is that the database is not reading the variable '|$1||', I believe that when it is read in SELECT its contents already come with ' (single quotes), example: SELECT * FROM dblink ('host=xx.xx.xx.xx dbname='variable''...

Can someone help me?

Abs, Lucas.

sql database postgresql query variables

  • What is the error message? Are you using sql or plpgsql? Can you send the full code or make an example in sqlfiddle.com?

  • It does not from the error message, the Insert is not performed in the target table. Example: DO $$ declare $1 VARCHAR; BEGIN IF(SELECT COUNT(*) FROM time2 WHERE looping IS NULL)> 0 Then $1 = (SELECT bank FROM time2 WHERE looping IS NULL LIMIT 1); INSERT INTO tableCreated(a ,b ,c ,d ,e ,f) SELECT * FROM dblink ('host=xx.xx.xx.xx port=x dbname='|$1|' user=x password=x', 'SELECT (SELECT a, b, c, d, e, f -- The body of select ta ok ) as x (a VARCHAR ,b VARCHAR ,c TIMESTAMP ,d VARCHAR, and VARCHAR ,f VARCHAR); UPDATE time2 set looping = ’T' WHERE bank = $1; END IF;END; $$;

  • If the problem is the quotation marks maybe quote_literal resolve

No answers

Browser other questions tagged sql database postgresql query variables

You are not signed in. Login or sign up in order to post.