Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE OR REPLACE FUNCTION union_foreign_tables(ft_info text[][])
RETURNS SETOF record
AS $$
DECLARE
    ft_row text[];
    ft_name text;
    ft_col1 text;
    ft_col2 text;
    ft_col3 text;
    ft_connected boolean;
    first_table boolean := true;
    query text := '';
    i integer;
BEGIN
    FOR i IN 1..array_length(ft_info, 1)
    LOOP
        ft_row := ft_info[i];
        ft_name := ft_row[1];
        ft_col1 := ft_row[2];
        ft_col2 := ft_row[3];
        ft_col3 := ft_row[4];

ft_connected := true;
        BEGIN
            EXECUTE format('SELECT 1 FROM %I LIMIT 1', ft_name);
        EXCEPTION WHEN others THEN
            ft_connected := false;
        END;

IF ft_connected THEN
            IF first_table THEN
                query := format('SELECT %I AS col1, %I AS col2, %I AS col3 FROM %I', ft_col1, ft_col2, ft_col3, ft_name);
                first_table := false;
            ELSE
                query := query || format(' UNION SELECT %I AS col1, %I AS col2, %I AS col3 FROM %I', ft_col1, ft_col2, ft_col3, ft_name);
            END IF;
        END IF;
    END LOOP;

IF query <> '' THEN
        RETURN QUERY EXECUTE query;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

union_foreign_tables

SELECT 0