title: postgresql weird invalid input syntax for type numeric: "" while value is not an empty varchar
body: <p>I am trying to debug a function not made by myself (<a href="https://spatialdbadvisor.com/postgis_tips_tricks/115/dms2dd-for-postgis" rel="noreferrer">dms2dd</a>). I made my own test function (see below) and boiled my problem down to a specific row/value.</p>
  
  <p>If I run the following query:</p>
  
  <pre><code>SELECT "Lat", "Long", test_dolf("Lat"), test_dolf("Long") FROM pawikan WHERE "Lat" IS NOT NULL AND "Long" IS NOT NULL ORDER BY index LIMIT 1 OFFSET 29130
  </code></pre>
  
  <p>I get the following output:</p>
  
  <pre><code>'N6° 6' 9.4824"';'E118° 26' 49.1172'' ';'9.4824';'49.1172'
  </code></pre>
  
  <p>which is exactly what I expect.
  But with the following query:</p>
  
  <pre><code>SELECT "Lat", "Long", CAST(test_dolf("Lat") as numeric), test_dolf("Long") FROM pawikan WHERE "Lat" IS NOT NULL AND "Long" IS NOT NULL ORDER BY index  LIMIT 1 OFFSET 29130
  </code></pre>
  
  <p>I get the error </p>
  
  <pre><code>ERROR: invalid input syntax for type numeric: ""
  SQL state: 22P02
  </code></pre>
  
  <p>The error suggests that the varchar value I was trying to cast to numeric is empty, but as you can see from the previous query, it isn't. It is just a valid numeric varchar. Actually, if I copy-paste the value and run:</p>
  
  <pre><code>SELECT CAST('9.4824' AS numeric);
  </code></pre>
  
  <p>It totally works and the query actually results in a valid numeric. 
  Even more, if I store the results of the first query in an intermediary table with:</p>
  
  <pre><code>SELECT "Lat", "Long", test_dolf("Lat") as lat_sec, test_dolf("Long") as long_sec INTO dms2dd_test FROM pawikan WHERE "Lat" IS NOT NULL AND "Long" IS NOT NULL ORDER BY index LIMIT 11 OFFSET 29120
  </code></pre>
  
  <p>and then issue a </p>
  
  <pre><code>SELECT CAST(long_sec as numeric), CAST(lat_sec AS numeric) FROM dms2dd_test;
  </code></pre>
  
  <p>it totally works.
  Even this works just fine:</p>
  
  <pre><code>SELECT test_dolf(E'N6° 6\' 9.4824"')::numeric as lat_sec
  </code></pre>
  
  <p>So what is going wrong here? It looks like in the second query where I cast to numeric, a different value is passed to my function, but I tested the sort column (index) and it contains only unique bigints.</p>
  
  <p>This is the code for the test_dolf function:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION public.test_dolf(strdegminsec character varying)
    RETURNS varchar 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);
      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) &gt; 0 Then
               -- add character but replace comma with point
               If (strChr &lt;&gt; ',') Then
                  strNorm := strNorm || strChr;
               Else
                  strNorm := strNorm || '.';
               End If;
               blnGotSeparator := 0;
            ElsIf strpos('neswNESW',strChr) &gt; 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, ' ');
         return arrDegMinSec[3];
      End 
  $BODY$
    LANGUAGE plpgsql IMMUTABLE
    COST 100;
  </code></pre>