Invalid input syntax for type double precision

Question

I've created functions and one of them is:

CREATE OR REPLACE FUNCTION core.cal_status(
  er_v                 DOUBLE PRECISION,
  cell_v               DOUBLE PRECISION
) RETURNS DOUBLE PRECISION
  LANGUAGE plpgsql
AS $$
DECLARE out DOUBLE PRECISION;
  BEGIN
    IF er_v < 15                                THEN out := 'LOW_LOAD';
    ELSEIF cell_v < 60                          THEN out := 'LOW_LOAD';
    ELSEIF er_v > 40                            THEN out := 'CONGESTED';
    ELSEIF cell_v > 80                          THEN out := 'CONGESTED';
    ELSEIF cell_v >60 and cell_v < 80           THEN out := 'HIGH_LOAD';
  END IF;
  RETURN out;
  END;
$$;

When I call functions with this:

LEFT JOIN LATERAL core.cal_status(
            er_v,
            cell_v) status ON true

I got next error:

ERROR: invalid input syntax for type double precision: "LOW_LOAD" Where: PL/pgSQL function core.cal_status(double precision,double precision) line 4 at assignment

What is this about, I guess it's something with output type, but not sure.

IMHO, RETURNS DOUBLE PRECISION means you need a double value after out := ;). — shA.t
Your function is declared to return a double precision. The actual type is text. The error is not surprising or misleading. — Gordon Linoff

Kevin Miranda Kevin Miranda · Accepted Answer · 2017-08-08T14:15:15

The error happens because the variable out is of text type when you do out :='LOW_LOAD' and the function is expecting to return a DOUBLE PRECISION.

Try

CREATE OR REPLACE FUNCTION core.cal_status(
  er_v                 DOUBLE PRECISION,
  cell_v               DOUBLE PRECISION
) RETURNS TEXT
  LANGUAGE plpgsql
AS $$
DECLARE out TEXT;
  BEGIN
    IF er_v < 15                                THEN out := 'LOW_LOAD';
    ELSEIF cell_v < 60                          THEN out := 'LOW_LOAD';
    ELSEIF er_v > 40                            THEN out := 'CONGESTED';
    ELSEIF cell_v > 80                          THEN out := 'CONGESTED';
    ELSEIF cell_v >60 and cell_v < 80           THEN out := 'HIGH_LOAD';
  END IF;
  RETURN out;
  END;
$$;

Invalid input syntax for type double precision

2 Answers