CODEWITHSUNDEEP
postgresqlsupabasesupabase-database
Milind Jamnekar
Milind Jamnekar

Reputation: 1

"structure of query does not match function result type" when returning table from supbase

I am completely new to supabase and postgresql. I wanted to get top 3 sneaker baught from sales_items database.

So i wrote this function

SELECT sneaker_product, SUM(quantity) as total_sales
   FROM store_sales_items
   WHERE is_sneaker is not null
   GROUP by sneaker_product
   ORDER BY total_sales DESC
   LIMIT 3;

that will return

sneaker_product total_sales
1 "10"
3 "6"
4 "5"

then i try to create function so i can use easily on react app. here it is

CREATE OR REPLACE FUNCTION top_three()
RETURNS TABLE (sneaker_product INT, total_sales INT) AS $$
BEGIN
   RETURN QUERY
   SELECT sneaker_products.id, SUM(quantity) as total_sales
   FROM store_sales_items
   JOIN sneaker_products on store_sales_items.sneaker_product = sneaker_products.id
   WHERE is_sneaker is not null
   GROUP BY sneaker_products.id
   ORDER BY total_sales DESC
   LIMIT 3;
END;
$$ LANGUAGE plpgsql;

after lots of error solving I came up this but still Failed to run sql query: structure of query does not match function result type

all i am doing this on supabase web app sql editor.

One more thing sneakerproduct has relation with sneaker_products db as well is that causing problem.Idk but can some one help me. Thanks 😊

tried exact matching types of col but no luck. tried to set sneakerproduct type as sneaker_proudcts db i thought might work but thought thats not the way to do it.

Upvotes: 0

Views: 378

Answers (2)

noy
noy

Reputation: 1

It seems that sum()'s return type varies depending on the type of its arguments. In my case, where the arg type was bigint, the return type was numeric.

https://www.postgresql.org/docs/16/functions-aggregate.html

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247400

The aggregate function sum returns bigint, not integer. Either change the function result type or cast the sum to integer.

Upvotes: 2

Related Questions