Function with case return too many columns error
Hello and merry Xmas !
I just discovered the power of function in supabase.
I wrote a SQL query with case to check if an
Thank you
I just discovered the power of function in supabase.
I wrote a SQL query with case to check if an
invoice is late based on created_at a paymentDelaybut return the current status if the invoice is payed, canceled or cashed, the query returns the right result and the exact number of columns than the originalinvoicetable
`
select * ,
case when extract(epoch from created_at) * 1000 +"paymentDelay" < extract(epoch from now()) * 1000 THEN 'LATE' END status,
case status when 'PAYED' THEN 'PAYED' END status,
case status when 'CASHED' THEN 'CASHED' END status,
case status when 'CANCELED' THEN 'CANCELED' END status
from invoice
```
So far so god the result is what i expect !
But, when i try to turn it into function
```
create or replace function getInvoicesWithStatus() returns setof invoice as $$
select *,
case when extract(epoch from created_at) * 1000 +"paymentDelay" < extract(epoch from now()) * 1000 THEN 'LATE' END status,
case status when 'PAYED' THEN 'PAYED' END status,
case status when 'CASHED' THEN 'CASHED' END status,
case status when 'CANCELED' THEN 'CANCELED' END status
from invoice
$$ language sql;
```
the returned result is
```
ERROR: 42P13: return type mismatch in function declared to return invoice
DETAIL: Final statement returns too many columns.
CONTEXT: SQL function "getinvoiceswithstatus"
```
i understand that "it's seems" to have more column, but the query only "mutate" the status column.
How can i change the query to look like it fit the invoice` table structure ?Thank you
