Supabase SQL Function: How to fix 'Failed to run sql query: structure of query does not match function result type' SQL error

Question

Supabase user here trying to return a SQL query that groups share sales of a stock by the stock symbol. Essentially the user can make multiple purchases of the same stock for varying amount of shares. On a dashboard page, I want to show that as one combined row as a sum of all of their purchased stocks.

Another post remarks the Supabase API has no SUM operator, which means I would have to make a function, then run it using supabase.rpc(...).

Made the query (has desired results) and function (created successfully) but ran into the above error Failed to run sql query: structure of query does not match function result type while running the function.

Created table:

create table trades (
  id bigint generated by default as identity primary key,
  account_id bigint references accounts(id) not null,
  symbol varchar(5) not null,
  name text not null,
  shares int not null,
  price numeric not null,
  inserted_at timestamp with time zone default current_timestamp not null
);

Created query (runs as desired):

SELECT name as grouped_name, symbol::text as grouped_symbol, price as grouped_price, SUM (shares) as grouped_sum
FROM trades
GROUP BY
    name, symbol, price
ORDER BY
    SUM (shares) DESC;

Created function with above query (successfully makes function):

CREATE or REPLACE function return_grouped_shares() 
    RETURNS table(grouped_name text, grouped_symbol text, grouped_price numeric, grouped_sum int) AS
$$
BEGIN 
RETURN QUERY
    SELECT  name as grouped_name, symbol::text as grouped_symbol, price as grouped_price, SUM (shares) as grouped_sum
    FROM trades
    GROUP BY
        name, symbol, price
    ORDER BY
        SUM (shares) DESC;
END
$$ LANGUAGE plpgsql;

Running function:

SELECT *
FROM   return_grouped_shares ();

yields error Failed to run sql query: structure of query does not match function result type.

What's going on here?

score 1 · Answer 1 · answered Aug 15 '23 at 07:17

Your current error is most likely the result of the function's declared return type not matching the actual result structure. The updated version of your function and query is as follows:

Code for the fucntion:

CREATE or REPLACE FUNCTION return_grouped_shares() 
    RETURNS TABLE(grouped_name text, grouped_symbol text, grouped_price numeric, grouped_sum int) AS
$$
BEGIN 
    RETURN QUERY
    SELECT name as grouped_name, symbol::text as grouped_symbol, price as grouped_price, SUM(shares) as grouped_sum
    FROM trades
    GROUP BY name, symbol, price;
END
$$ LANGUAGE plpgsql;

Query to run:

SELECT * FROM return_grouped_shares();

I tried your code but unfortunately ran into the same error: `Failed to run sql query: structure of query does not match function result type`. Honestly, I am not too eager to implement this feature, so I might just work on something else. Thank you very much for taking the time to write an answer though! — zaynb06, Aug 15 '23 at 21:42

Supabase SQL Function: How to fix 'Failed to run sql query: structure of query does not match function result type' SQL error

1 Answers1