Feature request

Describe the current behavior

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 errorERROR: 42P13.

-- Incorrect code (PLS should mark this as an error)-- return type incorrectcreatefunctiontest_error_42P13_type_column_mismatch(user_id uuid) returnsusers_hidden.users  language sqlas$$selectu.id,u.first_namefromusers_hidden.users uwhereu.id= user_id;$$;-- Correct codecreatefunctiontest_column_return(user_id uuid) returnsusers_hidden.users  language sqlas$$select*fromusers_hidden.users uwhereu.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)createfunctiontest_error_42P13_type_primitive_mismatch() returnsboolean  language sqlas$$select'uuid';$$;-- Correct codecreatefunctiontest_primitive_return() returnsboolean  language sqlas$$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 auuid instead ofint is returned).

More advanced type mismatches include tables and composite types.

Requested Feature/solution/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 ofrettype andargtype which might be helpful.

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