SQL functions: Detect return type mismatches (error 42P13)

[Donnerstagnacht]

Bug report

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

Describe the bug

If a user defines an sql function, that returns data, but the returned data does not match the declared return type in the sql function signature, postgres throws a return type mismatch error ERROR: 42P13.

-- Incorrect code (PLS should mark this as an error)
-- return type incorrect
create function test_error_42P13_type_column_mismatch(user_id uuid) returns users_hidden.users  language sql as
$$
select u.id, u.first_name from users_hidden.users u where u.id = user_id;
$$;

-- Correct code
create function test_column_return(user_id uuid) returns users_hidden.users  language sql as
$$
select * from users_hidden.users u where u.id = user_id;
$$;

Results in error:

Error: ERROR: 42P13: return type mismatch in function declared to return users_hidden.users DETAIL: Final statement returns too few columns. CONTEXT: SQL function "test_error_42P13_type_column_mismatch"

-- incorrect code (PLS should mark this as an error)
create function test_error_42P13_type_primitive_mismatch() returns boolean  language sql as
$$
select 'uuid';
$$;

-- Correct code
create function test_primitive_return() returns boolean  language sql as
$$
select true;
$$;

Results in error:

Error: ERROR: 42P13: return type mismatch in function declared to return boolean DETAIL: Actual return type is text. CONTEXT: SQL function "test_error_42p13_type_primitive_mismatch"

To Reproduce

1. Declare a table with DDL
2. Creaate a sql function returning a row type
3. Select a subset of fields instead of all fields with *

This is one of many type mismatch examples. Other simple type mismatches are if different primary types are returned (for example if a uuid instead of int is returned).

More advanced type mismatches include tables and composite types.

Expected behavior

The postgres language server should detect the type mismatch and mark the function as invalid (code), e.g. extensions like the vs code extensions should indicate the type mismatch error with a squiggly line and a helpful type error message.

It would be awesome if the PLS could support the following types (in order of relevance to me):

1. base/primitives
2. table
3. rowtype
4. setof rowtype
5. composite
6. table_name.column_name%TYPE

System information

• OS: windows AMD
• PLS: 0.8.1 with vs-code extension
• postgres supabase

Useful summary

Postgres create table docs displays some summary of rettype and argtype which might be helpful.

PS: Do you consider this a bug or a feature request?