search

PostgREST / postgrest-openapi

OpenAPI output generated in SQL for a PostgREST instance
MIT License
20 stars 4 forks source link

The functions from `postgrest.sql` are not very reusable #35

Closed laurenceisla closed 2 months ago

laurenceisla commented 9 months ago

The function postgrest_get_all_tables in particular, returns columns as a JSONB object, using functions from openapi.sql.

https://github.com/PostgREST/postgrest-openapi/blob/b5827bf3e70c5fcbb325e4d5f870c5f1879edae1/sql/postgrest.sql#L5-L18

I think the returning table should be normalized (by columns and its properties, not a JSONB object) and not use openapi.sql functions if necessary. Then we could use an intermediate function(s) to convert to different OpenAPI objects. This function could be the one called (instead of postgrest_get_all_tables) by other modules to build parameters, schemas, etc.

This would make it easier to reuse and even more performant (does not need to unnest or keep repeating unnecessary queries).

laurenceisla commented 9 months ago

Another idea is to merge postgrest_get_all_composite_types and postgrest_get_all_tables into one, since both are retrieved using similar queries and generate reusable schemas in the same way.

This is also convenient since FUNCTIONs can use composite types in their parameters and returning types (even tables). That way we can use the merged composite types/tables function and filter the ones that are used by exposed functions.