Type generation script does not pick up correct type when a SQL function returns data from other table

[travis-humata]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

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.

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;

generates type:

      get_areas_for_semantic_chunks: {
        Args: {
          _chunk_ids: string[]
        }
        Returns: {
          areas: unknown[]
        }[]
      }

whereas I would expect it to be:

      get_areas_for_semantic_chunks: {
        Args: {
          _chunk_ids: string[]
        }
        Returns: {
          areas: Database['public']['Tables']['semantic_chunk_areas']['Row']
        }[]
      }

To generate the types we are running this command:

npx supabase gen types typescript --project-id uokudzbrbkipvuxpkzhc --schema public > types/supabase.ts

To Reproduce

1. Create a SQL function that returns a table with one of the columns of the return table being an item from another table in your schema
2. Run the type genreration script
3. Note that the type for that column will be unknown even though the type is known

See snippet above

Expected behavior

The generated type will match the type of the existing table

[soedirgo]

Thanks! Resolved in #533, which has been deployed on the hosted platform & latest Supabase CLI.