Need help with postgres function

I want to sum the margin and sales quantity of my items and group them by its currency. I have created this function:

CREATE OR REPLACE FUNCTION get_sales_overview(design_id_input varchar) 
RETURNS TABLE(
  total_margin numeric,
  total_sales numeric,
  margin_currency varchar
) 
AS $$ 
  BEGIN
    RETURN QUERY 
    SELECT 
      sum(margin(stored as numeric)) as total_margin,
      sum(sold_quantity (stored as int4)) as total_sales,
      sales_items.margin_currency (stored as varchar)
    FROM sales_items
    WHERE sales_items.design_id = design_id_input
    GROUP BY
      sales_items.margin_currency;
  END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION get_sales_overview(design_id_input varchar) 
RETURNS TABLE(
  total_margin numeric,
  total_sales numeric,
  margin_currency varchar
) 
AS $$ 
  BEGIN
    RETURN QUERY 
    SELECT 
      sum(margin(stored as numeric)) as total_margin,
      sum(sold_quantity (stored as int4)) as total_sales,
      sales_items.margin_currency (stored as varchar)
    FROM sales_items
    WHERE sales_items.design_id = design_id_input
    GROUP BY
      sales_items.margin_currency;
  END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION get_sales_overview(design_id_input varchar) 
RETURNS TABLE(
  total_margin numeric,
  total_sales numeric,
  margin_currency varchar
) 
AS $$ 
  BEGIN
    RETURN QUERY 
    SELECT 
      sum(margin(stored as numeric)) as total_margin,
      sum(sold_quantity (stored as int4)) as total_sales,
      sales_items.margin_currency (stored as varchar)
    FROM sales_items
    WHERE sales_items.design_id = design_id_input
    GROUP BY
      sales_items.margin_currency;
  END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION get_sales_overview(design_id_input varchar) 
RETURNS TABLE(
  total_margin numeric,
  total_sales numeric,
  margin_currency varchar
) 
AS $$ 
  BEGIN
    RETURN QUERY 
    SELECT 
      sum(margin(stored as numeric)) as total_margin,
      sum(sold_quantity (stored as int4)) as total_sales,
      sales_items.margin_currency (stored as varchar)
    FROM sales_items
    WHERE sales_items.design_id = design_id_input
    GROUP BY
      sales_items.margin_currency;
  END;
$$ language plpgsql;

when I call

select * from get_sales_overview('B08YB5SBYX');

select * from get_sales_overview('B08YB5SBYX');

select * from get_sales_overview('B08YB5SBYX');

select * from get_sales_overview('B08YB5SBYX');

I get an error :

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

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

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

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

Where am I returning the wrong type?

garyaustin•12/8/22, 3:02 PM

numeric is not the same as int4.

WizzelOP•12/8/22, 3:09 PM

I thought numeric is some kind of wildcard for "numeric" types.

However I have changed the return type of

total_sales numeric

total_sales numeric

total_sales int4

total_sales int4

and I still get the same error

WizzelOP•12/8/22, 3:16 PM

Ah well, it seems like

sum(sold_quantity (stored as int4))

sum(sold_quantity (stored as int4))

returns a bigint in this case.

Is there any documentation where I could have read about this?

garyaustin•12/8/22, 3:18 PM

You should be able to cast it ::int4 at end
just google postgres and sum I'm sure there are hits... The postgres docs are very extensive.

WizzelOP•12/8/22, 3:22 PM

Okay, thank you!

Need help with postgres function

Similar Threads

Similar Threads

Similar Threads