Generate SQL join queries based on foreign keys

pg-join-generator.sql

SELECT 'SELECT * FROM ' || src_table || ' JOIN ' || dst_table || ' ON (' ||
       string_agg(src_table || '.' || src_column || ' = ' || dst_table || '.' || dst_column, ' AND ') || ')'
  FROM (SELECT quote_ident(kcu.table_schema) || '.' || quote_ident(kcu.table_name) AS src_table,
               quote_ident(rcu.table_schema) || '.' || quote_ident(rcu.table_name) AS dst_table,
               quote_ident(kcu.column_name) AS src_column,
               quote_ident(rcu.column_name) AS dst_column
        FROM information_schema.referential_constraints rc
        LEFT JOIN information_schema.key_column_usage kcu
          USING (constraint_catalog, constraint_schema, constraint_name)
        LEFT JOIN information_schema.key_column_usage rcu -- referenced columns
          ON rc.unique_constraint_catalog = rcu.constraint_catalog
          AND rc.unique_constraint_schema = rcu.constraint_schema
          AND rc.unique_constraint_name = rcu.constraint_name
          AND rcu.ordinal_position = kcu.position_in_unique_constraint
        WHERE rcu.column_name IS NOT NULL) s
 GROUP BY src_table, dst_table;