SQLize Online / PHPize Online  /  SQLtest Online

WITH exemplo as ( SELECT '{"allowlist":[{"collection":"allowed-queries","scope":{"global":true}}],"sources":[{"kind":"postgres","name":"UPADS","tables":[{"select_permissions":[{"role":"bla","permission":{"columns":["id","nome"],"filter":{}}},{"role":"master","permission":{"columns":["id","nome"],"filter":{}}}],"insert_permissions":[{"role":"bla","permission":{"backend_only":false,"check":{},"columns":["nome"]}},{"role":"master","permission":{"backend_only":false,"check":{},"columns":["nome"]}}],"table":{"schema":"rmcontact","name":"l001"},"update_permissions":[{"role":"bla","permission":{"check":null,"columns":["id","nome"],"filter":{}}},{"role":"master","permission":{"check":null,"columns":["nome"],"filter":{}}}],"delete_permissions":[{"role":"bla","permission":{"filter":{}}},{"role":"master","permission":{"filter":{}}}]},{"select_permissions":[{"role":"bla","permission":{"columns":["created_at","roles","updated_at","username"],"filter":{}}},{"role":"master","permission":{"columns":["created_at","roles","updated_at","username"],"filter":{}}}],"insert_permissions":[{"role":"master","permission":{"backend_only":false,"check":{},"columns":["created_at","roles","updated_at","username"]}},{"role":"teste2","permission":{"backend_only":false,"check":{},"columns":["created_at","roles","updated_at","username"]}}],"table":{"schema":"rmcontact","name":"users"},"update_permissions":[{"role":"master","permission":{"check":null,"columns":["created_at","roles","updated_at","username"],"filter":{}}}],"delete_permissions":[{"role":"master","permission":{"filter":{}}}]}],"configuration":{"connection_info":{"use_prepared_statements":false,"database_url":"postgres://upads:1@postgres:5432/upads","isolation_level":"read-committed"}}}],"version":3,"query_collections":[{"definition":{"queries":[]},"name":"allowed-queries"}]}'::jsonb as metadata ) SELECT upper(db.json #>> '{kind}') AS dsn, upper(db.json #>> '{name}') AS nome_banco, upper(tbl.json #>> '{table,schema}') AS schema, upper(tbl.json #>> '{table,name}') AS nome_tabela, perms.* FROM exemplo AS a CROSS JOIN LATERAL jsonb_path_query(a.metadata, '$.sources[*]') as db(json) CROSS JOIN LATERAL jsonb_path_query(db.json,'$.tables[*]') AS tbl(json) CROSS JOIN LATERAL ( SELECT t.role, bool_or(t.sel) AS sel, bool_or(t.ins) AS ins, bool_or(t.upd) AS upd, bool_or(t.del) AS del FROM ( SELECT upper(jsonb_array_elements(tbl.json -> 'select_permissions')->>'role') AS role, true AS sel, false AS ins, false AS upd, false AS del UNION ALL SELECT upper(jsonb_array_elements(tbl.json -> 'insert_permissions')->>'role'), false, true, false, false UNION ALL SELECT upper(jsonb_array_elements(tbl.json -> 'update_permissions')->>'role'), false, false, true, false UNION ALL SELECT upper(jsonb_array_elements(tbl.json -> 'delete_permissions')->>'role'), false, false, false, true ) AS t GROUP BY t.role ) AS perms ;