CODEWITHSUNDEEP
sqlpostgresqlddlstored-functions
jdesilvio
jdesilvio

Reputation: 1854

PostgreSQL function numeric type error

I am trying to create a Stored Procedure in PostgreSQL. The function works when I use float, but when I try to change the floats to numeric I get an error: 

ERROR: cannot change return type of existing function
SQL state: 42P13
Detail: Row type defined by OUT parameters is different.
Hint: Use DROP FUNCTION percent_grades(text) first.

I don't really know what that means, but I don't think that I'd need to do anything other than CAST to change the type.

My function is:

CREATE OR REPLACE FUNCTION percent_grades(text)
RETURNS TABLE(grade text, percent float)
AS
  $$
    DECLARE percent_total float;
    BEGIN
      RETURN QUERY
        SELECT psa_grade_name, 
               SUM(psa_amount) / CAST(total_cards($1) AS float) AS percent_total
        FROM psa_pop_view
        WHERE psa_card = $1
        GROUP BY psa_grade_name
        ORDER BY psa_grade_name;
    END;
  $$
LANGUAGE plpgsql;

I want to replace the floats with numeric(5, 4).

Upvotes: 10

Views: 13682

Answers (1)

Mureinik
Mureinik

Reputation: 311326

a create or replace statement cannot change the signature of a function. As the error says, you must first drop the function:

DROP FUNCTION percent_grades(text);

And only then re-create it with the right signature:

CREATE OR REPLACE FUNCTION percent_grades(text)
RETURNS TABLE(grade text, percent numeric(5,4))
AS
  $$
    DECLARE percent_total float;
    BEGIN
      RETURN QUERY
        SELECT psa_grade_name, 
               SUM(psa_amount) / CAST(total_cards($1) AS float) AS percent_total
        FROM psa_pop_view
        WHERE psa_card = $1
        GROUP BY psa_grade_name
        ORDER BY psa_grade_name;
    END;
  $$
LANGUAGE plpgsql;

Upvotes: 15

Related Questions