postgresql weird invalid input syntax for type numeric: "" while value is not an empty varchar

I figured out what the problem was. It looks like postgresql, even though I do a LIMIT and OFFSET, still calls the functions in the select for other rows outside that frame.

I figured this out by putting the code that raised the exception inside my function and catching the resulting error, and raising a NOTICE error when that exception occurs (see function below, specifically the BEGIN EXCEPTION END block at the end of the function). The notice is displayed as a warning, but doesn't cause the code execution to stop. All of a sudden it turned out that the function wasn't just called for the row that I was expecting it to be called for, but also for a whole bunch of other rows. This is totally not what I expected, and for me kind of counter intuitive, but I guess it is how postgresql is supposed to work.

Since catching exceptions is quite expensive in postgresql, I guess I need to add a test which prevents the exception in the first place (I could test the length of arrDegMinSec and the value of items 1-3 of that array and return NULL in case of invalid values.

CREATE OR REPLACE FUNCTION public.test_dolf(strdegminsec character varying)
  RETURNS numeric AS
$BODY$
    DECLARE
       i               numeric;
       intDmsLen       numeric;          -- Length of original string
       strCompassPoint Char(1);
       strNorm         varchar(16) = ''; -- Will contain normalized string
       strDegMinSecB   varchar(100);
       blnGotSeparator integer;          -- Keeps track of separator sequences
       arrDegMinSec    varchar[];        -- TYPE stringarray is table of varchar(2048) ;
       strChr          Char(1);
       retval          numeric;
    BEGIN

       strDegMinSec := regexp_replace(replace(strdegminsec,E'\'\'','"'),' "([0-9]+)',E' \\1"');
       -- Remove leading and trailing spaces
       strDegMinSecB := REPLACE(strDegMinSec,' ','');
       intDmsLen := Length(strDegMinSecB);

       blnGotSeparator := 0; -- Not in separator sequence right now

       -- Loop over string, replacing anything that is not a digit or a
       -- decimal separator with
       -- a single blank
       FOR i in 1..intDmsLen LOOP
          -- Get current character
          strChr := SubStr(strDegMinSecB, i, 1);
          -- either add character to normalized string or replace
          -- separator sequence with single blank         
          If strpos('0123456789,.', strChr) > 0 Then
             -- add character but replace comma with point
             If (strChr <> ',') Then
                strNorm := strNorm || strChr;
             Else
                strNorm := strNorm || '.';
             End If;
             blnGotSeparator := 0;
          ElsIf strpos('neswNESW',strChr) > 0 Then -- Extract Compass Point if present
            strCompassPoint := strChr;
          Else
             -- ensure only one separator is replaced with a blank -
             -- suppress the rest
             If blnGotSeparator = 0 Then
                strNorm := strNorm || ' ';
                blnGotSeparator := 0;
             End If;
          End If;
       End Loop;
       -- Split normalized string into array of max 3 components
       arrDegMinSec := string_to_array(strNorm, ' ');
       BEGIN
          retval := arrDegMinSec[3]::numeric;
          return retval;
       EXCEPTION
          WHEN SQLSTATE '22P02' THEN
             RAISE NOTICE 'Incorrect value %', strDegMinSec;
             RETURN NULL;
       END;
    End 
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;

EDIT

Courtesy of @michel.milezzi another solution that doesn't require modification of the function is to change the function call in the query to

CAST(NULLIF(test_dolf("Lat"), '') as numeric)

And indeed as @abelisto suggests, I could also have put the query in a subquery and only casting it to numeric in the main query like this:

SELECT "Lat", "Long", CAST(test_dolf("Lat") as numeric), test_dolf("Long") FROM (SELECT * FROM pawikan WHERE "Lat" IS NOT NULL AND "Long" IS NOT NULL ORDER BY index LIMIT 1 OFFSET 29130) as t

This would indeed have prevented the problem, which would have indeed simplified the debugging process.

That being said, I was going to modify the function anyway (to make it more robust for dirty data) so for me that was in this case the best solution.