Create dblink connection string from variables in PostgesSQL

Question

DO $$ 
DECLARE 
_host TEXT := 'localhost';
_port TEXT := '5432';
_user TEXT := 'postgres';
_pass TEXT := 'postgres';
_db TEXT := 'schema';
BEGIN 
CREATE OR REPLACE VIEW companies AS
SELECT
    id
FROM
    dblink(
        'postgresql://' || _user || ':' || _pass || '@' || _host || ':' || _port || '/' || _db,
        'select id from company'
    ) AS t1(
        id TEXT
    );

DROP VIEW IF EXISTS companies CASCADE;
COMMIT;
END $$;

I want create string config from variable below

'postgresql://postgres:postgres@localhost:5432/schema'

Answer 1

The issue isn't the dblink but the view creation. You can't directly use a variable to define it. You can however execute a text which happens to contain the view creation statement, and to create such statement by concatenating your variables.

Here is a simplified example. Note the use of a second dollar quoted string ($QRY$) to avoid fiddling with quote escaping.

do $$
declare 
_qry TEXT :='1';
begin
 EXECUTE $QRY$ create or replace view test as select $QRY$ || quote_literal(_qry);
end $$;