docteurklein · September 20, 2021 07:56 · docteurklein · Sep 20, 2021
diff --git a/traverse.sql b/traverse.sql
 ;

 drop function if exists explore(text);
 create function explore(sql text) returns setof record
 language 'plpgsql' as $$
 begin 
    return query execute format($sql$
        with node (tableoid, row) as (
            %s
        ),
        relation as (
            select 
                n.row,
                c.conname as rel,
                pg_get_constraintdef(c.oid) as def, 
                jsonb_object_agg(
                    f_col.attname,
                    row_to_json(n.row)->(col.attname)
                ) params
            from node n
            left join pg_constraint c on (c.conrelid = n.tableoid)

            left join unnest(c.conkey) with ordinality as u(attnum, attposition) on true
            left join pg_attribute col on (col.attrelid = n.tableoid and col.attnum = u.attnum)

            left join unnest(c.confkey) with ordinality as f_u(attnum, attposition) on f_u.attposition = u.attposition
            left join pg_attribute f_col on (f_col.attrelid = c.confrelid and f_col.attnum = f_u.attnum)
            left join pg_class f_tbl on f_tbl.oid = c.confrelid
            where c.contype = 'f'
            group by n.row, rel, def
            union
            select 
                n.row,
                c.conname as rel,
                pg_get_constraintdef(c.oid) as def, 
                jsonb_object_agg(
                    f_col.attname,
                    row_to_json(n.row)->(col.attname)
                ) params
            from node n
            left join pg_constraint c on (c.confrelid = n.tableoid)

            left join unnest(c.conkey) with ordinality as u(attnum, attposition) on true
            left join pg_attribute col on (col.attrelid = n.tableoid and col.attnum = u.attnum)

            left join unnest(c.confkey) with ordinality as f_u(attnum, attposition) on f_u.attposition = u.attposition
            left join pg_attribute f_col on (f_col.attrelid = c.confrelid and f_col.attnum = f_u.attnum)
            left join pg_class f_tbl on f_tbl.oid = c.confrelid
            where c.contype = 'f'
            group by n.row, rel, def
        )
        select
            row_to_json(row)::jsonb doc,
            jsonb_object_agg(rel, params) links
        from relation
        group by row
 $sql$, sql); 
 end 
 $$;
 -- select * from explore('select tableoid, n from product n limit 10') as _(doc jsonb, links jsonb);
	;

	drop function if exists explore(text);
	create function explore(sql text) returns setof record
	language 'plpgsql' as $$
	begin
	return query execute format($sql$
	with node (tableoid, row) as (
	%s
	),
	relation as (
	select
	n.row,
	c.conname as rel,
	pg_get_constraintdef(c.oid) as def,
	jsonb_object_agg(
	f_col.attname,
	row_to_json(n.row)->(col.attname)
	) params
	from node n
	left join pg_constraint c on (c.conrelid = n.tableoid)

	left join unnest(c.conkey) with ordinality as u(attnum, attposition) on true
	left join pg_attribute col on (col.attrelid = n.tableoid and col.attnum = u.attnum)

	left join unnest(c.confkey) with ordinality as f_u(attnum, attposition) on f_u.attposition = u.attposition
	left join pg_attribute f_col on (f_col.attrelid = c.confrelid and f_col.attnum = f_u.attnum)
	left join pg_class f_tbl on f_tbl.oid = c.confrelid
	where c.contype = 'f'
	group by n.row, rel, def
	union
	select
	n.row,
	c.conname as rel,
	pg_get_constraintdef(c.oid) as def,
	jsonb_object_agg(
	f_col.attname,
	row_to_json(n.row)->(col.attname)
	) params
	from node n
	left join pg_constraint c on (c.confrelid = n.tableoid)

	left join unnest(c.conkey) with ordinality as u(attnum, attposition) on true
	left join pg_attribute col on (col.attrelid = n.tableoid and col.attnum = u.attnum)

	left join unnest(c.confkey) with ordinality as f_u(attnum, attposition) on f_u.attposition = u.attposition
	left join pg_attribute f_col on (f_col.attrelid = c.confrelid and f_col.attnum = f_u.attnum)
	left join pg_class f_tbl on f_tbl.oid = c.confrelid
	where c.contype = 'f'
	group by n.row, rel, def
	)
	select
	row_to_json(row)::jsonb doc,
	jsonb_object_agg(rel, params) links
	from relation
	group by row
	$sql$, sql);
	end
	$$;
	-- select * from explore('select tableoid, n from product n limit 10') as _(doc jsonb, links jsonb);