CODEWITHSUNDEEP
sqlpostgresqlcasting
jovicbg
jovicbg

Reputation: 1553

Invalid input syntax for type double precision

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.

Upvotes: 0

Views: 27336

Answers (2)

Shah Vipul
Shah Vipul

Reputation: 747

In Django it happens when you don't migrate after makemigrations

Upvotes: -2

Kevin Miranda
Kevin Miranda

Reputation: 182

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;
$$;

Upvotes: 1

Related Questions