Postgresql Function with loop should continue on error

Question

Let's say I have table with connections. Postgres v10.2

CREATE TABLE connections(id integer,host varchar,users varchar,password varchar, DB varchar, port integer); 
INSERT INTO connections VALUES

(1,  'server1.com','user1','pw1','db1',5432),
(2,  'server2.com','user2','pw2','db2',5432),
(3,  'server3.commm','user3','pw3','db3',5432),
(4,  'server4.com','user4','pw4','db4',5432);

Demo

Goal is to loop thru this 4 servers and return the amount of users on the server. I have a function that does the job if the connections are ok but I don't know how I can handle the problem when one of this connection fails.

In this case 'server3.commm' would fail and my whole function returns an error.

I added the column status on my existing function to show you what I'm trying to do.

CREATE OR REPLACE FUNCTION dblink_function()
 RETURNS TABLE(host character varying,total integer, status character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    var_req TEXT;

    rec_key record;
    cur_key CURSOR FOR 
select id,host,users,password,db,port from connections
;

BEGIN
open cur_key;
loop
   fetch cur_key into rec_key;
   EXIT WHEN NOT FOUND;

return query execute 'Select * from dblink(
''host=' || rec_key.host || '
user=' || rec_key.users || '
password=' || rec_key.password || '
dbname=' || rec_key.db || '
port=' || rec_key.port || ',
''
select
''''' || rec_key.host || ''''' as host ,
count(*) as total,
case when ''connection ok'' then ''OK'' ELSE ''connection failure'' as status
from users
'') as (host varchar,total integer, status varchar);
';

 end loop;
close cur_key;
END
$function$
;

I don't wan't the my function to crash when a connection fails. I want it to continue and give me the following output. (Total is null because the connection is to do the query is not possible)

host             total    status
server1.com       5         ok
server2.com       5         ok
server3.com      null    connection failed
server4.com       5         ok

So i came up with an Solution on my own.

Here it is:

CREATE OR REPLACE FUNCTION dblink_function()
 RETURNS TABLE(host character varying,total integer, status character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    var_req TEXT;

    rec_key record;
    cur_key CURSOR FOR 
select id,host,users,password,db,port from connections
;

BEGIN
open cur_key;
loop
   fetch cur_key into rec_key;
   EXIT WHEN NOT FOUND;
begin
    var_req := 'Select * from dblink(
''host=' || rec_key.host || '
user=' || rec_key.users || '
password=' || rec_key.password || '
dbname=' || rec_key.db || '
port=' || rec_key.port || ',
''
select
''''' || rec_key.host || ''''' as host ,
count(*) as total,
''ok'' as status
from users
'') as (host varchar,total integer, status varchar);
';
return query execute var_req;
exception when others then
return query execute 'select
''' || rec_key.host || ''' as host ,
null as total,
''Connection failed'' as status
';
end;
 end loop;
close cur_key;
END
$function$
;

Answer 1

I checked your query, your query has one exception. PostgreSQL default using text type when you write manual string on the select statement. In your query has this statement: ''Connection failed'' as status. But in returns fields, you write status character varying. You can write that:

return query execute 'select
''' || rec_key.host || ''' as host ,
null as total,
''Connection failed''::character varying as status
';

I have an example similar to your query:

CREATE OR REPLACE FUNCTION test.select_from_all_tables()
RETURNS table (p_id integer, pstatus character varying)
LANGUAGE plpgsql
AS $function$
declare
    v_index integer;
    sql_create text;
    sql_create_error text;
    rec_key record;
    cur_key CURSOR FOR 
    select table_names from test.list_tables order by id;
begin
    sql_create = 'select id, ''succes''::character varying from %s limit 1';
    sql_create_error = 'select 0, ''error''::character varying';

    open cur_key;
    loop
        fetch cur_key into rec_key;
        EXIT WHEN NOT FOUND;    
        begin 
            return query 
            execute format(sql_create, rec_key.table_names);
        exception when others then 
            return query 
            execute sql_create_error;
        end;
    end loop;
    close cur_key;
END;
$function$
;