When we define SQL functions to return a value from another table they are typed as unknown instead of picking up the type from that table e.g.
CREATE OR REPLACE FUNCTION get_areas_for_semantic_chunks(_chunk_ids UUID[])
RETURNS TABLE (areas public.semantic_chunk_areas[]) AS $$
BEGIN
RETURN QUERY
SELECT array_agg(areas.* ORDER BY areas.text_start_index)
FROM semantic_chunk_to_semantic_chunk_areas AS join_table
JOIN semantic_chunk_areas AS areas
ON join_table.chunk_area_id = areas.id
WHERE join_table.chunk_id = ANY(_chunk_ids);
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;
Bug report
Describe the bug
When we define SQL functions to return a value from another table they are typed as unknown instead of picking up the type from that table e.g.
generates type:
whereas I would expect it to be:
To generate the types we are running this command:
To Reproduce
See snippet above
Expected behavior
The generated type will match the type of the existing table