Something went wrong. Try again.
a tool for shared writing and social publishing
Something went wrong. Try again.
3.1 kB · 100 lines
SQL
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101CREATE OR REPLACE FUNCTION public.get_leaflet_page_data(p_token_id uuid)RETURNS TABLE ( permission_token json, permission_token_rights json, leaflets_in_publications json, leaflets_to_documents json, custom_domain_routes json, facts json)LANGUAGE sql STABLEAS $$WITH token AS ( SELECT pt.* FROM permission_tokens pt WHERE pt.id = p_token_id),token_rights AS ( SELECT json_agg(row_to_json(ptr)) AS rights, array_agg(ptr.entity_set) AS entity_sets FROM permission_token_rights ptr WHERE ptr.token = p_token_id),related_tokens AS ( SELECT array_agg(pt2.id) AS ids FROM permission_token_rights ptr2 JOIN permission_tokens pt2 ON pt2.id = ptr2.token WHERE ptr2.entity_set IN (SELECT unnest(entity_sets) FROM token_rights) AND pt2.id != p_token_id),lip_direct AS ( SELECT json_agg(row_to_json(sub)) AS data FROM ( SELECT lip.*, row_to_json(pub) AS publications, row_to_json(d) AS documents FROM leaflets_in_publications lip LEFT JOIN publications pub ON pub.uri = lip.publication LEFT JOIN documents d ON d.uri = lip.doc WHERE lip.leaflet = p_token_id ) sub),lip_fallback AS ( SELECT json_agg(row_to_json(sub)) AS data FROM ( SELECT lip.*, row_to_json(pub) AS publications, row_to_json(d) AS documents FROM leaflets_in_publications lip LEFT JOIN publications pub ON pub.uri = lip.publication LEFT JOIN documents d ON d.uri = lip.doc WHERE lip.leaflet IN (SELECT unnest(ids) FROM related_tokens) ) sub),ltd_direct AS ( SELECT json_agg(row_to_json(sub)) AS data FROM ( SELECT ltd.*, row_to_json(doc) AS documents FROM leaflets_to_documents ltd LEFT JOIN documents doc ON doc.uri = ltd.document WHERE ltd.leaflet = p_token_id ) sub),ltd_fallback AS ( SELECT json_agg(row_to_json(sub)) AS data FROM ( SELECT ltd.*, row_to_json(doc) AS documents FROM leaflets_to_documents ltd LEFT JOIN documents doc ON doc.uri = ltd.document WHERE ltd.leaflet IN (SELECT unnest(ids) FROM related_tokens) ) sub),cdr AS ( SELECT json_agg(row_to_json(c)) AS data FROM custom_domain_routes c WHERE c.edit_permission_token = p_token_id),facts AS ( SELECT json_agg(row_to_json(f)) AS data FROM get_facts((SELECT root_entity FROM token)) f)SELECT row_to_json(token) AS permission_token, (SELECT rights FROM token_rights) AS permission_token_rights, COALESCE((SELECT data FROM lip_direct), (SELECT data FROM lip_fallback)) AS leaflets_in_publications, COALESCE((SELECT data FROM ltd_direct), (SELECT data FROM ltd_fallback)) AS leaflets_to_documents, (SELECT data FROM cdr) AS custom_domain_routes, (SELECT data FROM facts) AS factsFROM token;$$;
-- Indexes for get_leaflet_page_data query patternsCREATE INDEX IF NOT EXISTS leaflets_in_publications_leaflet_idx ON public.leaflets_in_publications(leaflet);
CREATE INDEX IF NOT EXISTS permission_token_rights_entity_set_idx ON public.permission_token_rights(entity_set);
CREATE INDEX IF NOT EXISTS custom_domain_routes_edit_permission_token_idx ON public.custom_domain_routes(edit_permission_token);