The functions from postgrest.sql are not very reusable

[laurenceisla]

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

postgrest-openapi/sql/postgrest.sql

Lines 5 to 18 in b5827bf

	create or replace function postgrest_get_all_tables(schemas text[])
	returns table (
	table_schema text,
	table_name text,
	table_description text,
	is_view bool,
	insertable bool,
	updatable bool,
	deletable bool,
	pk_cols text[],
	composite_cols oid[],
	required_cols text[],
	columns jsonb
	) language sql as

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]

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.