Access Path Suggestor - Postgresql

Here is an improved access path suggestor from postgresql metadata. Based from my Oracle version. In addition to visualized path alternative this generates a query based on the found access path. Could be used in getting familiar with a new data model. Should work also with foreign keys with multiple columns. Cycle detection missing. Just give names of the tables whose path alternatives you are interested in as :foo and :bar binds.

WITH RECURSIVE fks as (
SELECT cc.fk_schematable,
       cc.fk_table,
       string_agg(a.attname, ',' ORDER BY x.n) AS fk_columns,
       cc.conname AS constraint_name,
       cc.uk_schematable,
       cc.uk_table,
       string_agg(b.attname, ',' ORDER BY y.n) AS pk_columns,
       string_agg(cc.uk_table||'.'||b.attname||' = '||cc.fk_table||'.'||a.attname, ' and ' ORDER BY y.n) AS joi
  FROM (
  SELECT c.conrelid::regclass as uk_schematable,
         pk.relname uk_table,
         c.conname,
         c.confrelid::regclass as fk_schematable,
         fk.relname fk_table,
         c.conkey,
         c.confkey,
         c.conrelid,
         c.confrelid
    FROM pg_catalog.pg_constraint c
   INNER JOIN pg_catalog.pg_class pk ON c.conrelid!=0 AND c.conrelid=pk.oid
   INNER JOIN pg_catalog.pg_class fk ON c.confrelid!=0 AND c.confrelid=fk.oid
   WHERE c.contype = 'f'
  ) cc
   /* enumerated key column numbers per foreign key */
 CROSS JOIN LATERAL
      unnest(cc.conkey) WITH ORDINALITY AS x(attnum, n)
   /* name for each foreign key column */
  JOIN pg_catalog.pg_attribute a
    ON a.attnum = x.attnum
   AND a.attrelid = cc.conrelid
 CROSS JOIN LATERAL
      unnest(cc.confkey) WITH ORDINALITY AS y(attnum, n)
   /* name for each unique key column */
  JOIN pg_catalog.pg_attribute b
    ON b.attnum = y.attnum
   AND b.attrelid = cc.confrelid
 WHERE x.n=y.n 
 GROUP BY cc.fk_schematable, cc.fk_table, cc.conname, cc.uk_schematable, cc.uk_table
), pths as (
SELECT uk_schematable as o1,uk_table as t1,constraint_name,fk_schematable as o2,fk_table as t2,'-<' dir, joi
  FROM fks
 UNION ALL
SELECT fk_schematable as o1,fk_table as t1,constraint_name,uk_schematable as o2,uk_table as t2,'>-' dir, joi
  FROM fks
), rcte(o1,t1,constraint_name,o2,t2,dir,lvl,pth,fro,joi) as (
SELECT o1,t1,constraint_name,o2,t2,dir,1 lvl,o1||dir||o2 pth, o1||' '||t1||', '||o2||' '||t2, joi
  FROM pths
 WHERE t1 = :foo
 UNION ALL 
SELECT s.o1,s.t1,s.constraint_name,s.o2,s.t2
     , s.dir,lvl+1
     , prio.pth||s.dir||s.o2
     , prio.fro||', '||s.o2||' '||s.t2
     , prio.joi||' and '||s.joi
 FROM rcte prio INNER JOIN pths s ON prio.o2=s.o1 AND prio.constraint_name != s.constraint_name
WHERE prio.lvl < 30
)
SELECT pth,'select * from '||fro||' where '||joi||';' sq
  FROM rcte
 WHERE t2 = :baz
 ORDER BY lvl,pth
;

Testing

create table foo(i int primary key);

create table bar(j int primary key, i int references foo);

create table baz(k int primary key, j int references bar);

pth             sq
foo>-bar>-baz select * from foo foo, bar bar, baz baz where bar.i = foo.i and baz.j = bar.j;