Skip to content

Instantly share code, notes, and snippets.

@docteurklein
Last active September 20, 2021 07:56
Show Gist options
  • Save docteurklein/3080899709712ed0059d0cffb8b1b026 to your computer and use it in GitHub Desktop.
Save docteurklein/3080899709712ed0059d0cffb8b1b026 to your computer and use it in GitHub Desktop.
postgres query to generate links based on relations
;
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);
@docteurklein
Copy link
Author

result:

─[ RECORD 2 ]──────────────────────────────────────────────────────────
doc   │ {                                                              ↵
      │     "values": {                                                ↵
      │         "attribute#3": "test1"                                 ↵
      │     },                                                         ↵
      │     "family_id": "c6750835-8d22-4f1c-a002-e1aa8df0a052",       ↵
      │     "tenant_id": "3b639017-1ae0-4d67-a5ec-68189f2ff38e",       ↵
      │     "pk_product_id": "19daf31e-159d-4118-bc45-75f487daefd3"    ↵
      │ }
links │ {                                                              ↵
      │     "product_tenant_id_fkey": {                                ↵
      │         "tenant_id": "3b639017-1ae0-4d67-a5ec-68189f2ff38e"    ↵
      │     },                                                         ↵
      │     "product_family_id_tenant_id_fkey": {                      ↵
      │         "family_id": "c6750835-8d22-4f1c-a002-e1aa8df0a052",   ↵
      │         "tenant_id": "3b639017-1ae0-4d67-a5ec-68189f2ff38e"    ↵
      │     },                                                         ↵
      │     "product_in_category_product_id_tenant_id_fkey": {         ↵
      │         "tenant_id": "c6750835-8d22-4f1c-a002-e1aa8df0a052",   ↵
      │         "pk_product_id": "19daf31e-159d-4118-bc45-75f487daefd3"↵
      │     }                                                          ↵
      │ }
:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment