dynamic dblink in postgresql-CodePudding

i have this piece of code :

do $$
declare 
port int;
dbname varchar;
begin
    select current_database() into dbname;

    select setting into port from pg_catalog.pg_settings where name = 'port';

    perform format ('select dblink_connect(''toto'', ''port=%s dbname=%s'')', port, dbname);

    perform dblink_disconnect('toto');
end $$;

But when i run it, it says that my 'toto' connection does not exists when trying to disconnect it.

I've tried multiple things but nothing worked. I think there is something wrong with my dblink_connect, somehow it does not create the connection.

thanks for your help.

CodePudding user response：

You PERFORM the format function, obtaining a text result which looks like an SQL query, which is then thrown away. Because that is what PERFORM does.

Maybe you intended to EXECUTE that string, rather than throwing it away. But there is no reason to use dynamic queries here in the first place that I can see. You can use "format" in static queries too.

do $$
declare 
port int;
dbname varchar;
begin
    select current_database() into dbname;
    select setting into port from pg_catalog.pg_settings where name = 'port';
    perform dblink_connect('toto', format('port=%s dbname=%s', port, dbname));
    perform dblink_disconnect('toto');
end $$;