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

invoice

invoice

is late based on

created_at

created_at

a paymentDelay

a paymentDelay

but 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 original

 invoice

 invoice

table

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

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;

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"

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

invoice

table structure ?

Thank you

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

invoice

invoice

is late based on

created_at

created_at

a paymentDelay

a paymentDelay

but 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 original

 invoice

 invoice

table

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

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;

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"

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

invoice

table structure ?

Thank you

Function with case return too many columns error

Function with case return too many columns error

Similar Threads

Similar Threads

Similar Threads