postgresql奇怪的类型为numeric的无效输入语法：“”而value不是

如果我运行以下查询：

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

我得到以下输出：

'N6° 6' 9.4824"';'E118° 26' 49.1172'' ';'9.4824';'49.1172'

这正是我所期待的.
但是使用以下查询：

SELECT "Lat",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

我收到了错误

ERROR: invalid input syntax for type numeric: ""
SQL state: 22P02

该错误表明我尝试转换为数字的varchar值为空,但正如您从上一个查询中看到的那样,它不是.它只是一个有效的数字varchar.实际上,如果我复制粘贴值并运行：

SELECT CAST('9.4824' AS numeric);

它完全有效,查询实际上会生成一个有效的数字.
更重要的是,如果我将第一个查询的结果存储在中间表中：

SELECT "Lat",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

然后发出一个

SELECT CAST(long_sec as numeric),CAST(lat_sec AS numeric) FROM dms2dd_test;

它完全有效.
即使这样也可以：

SELECT test_dolf(E'N6° 6' 9.4824"')::numeric as lat_sec

那么这里出了什么问题？它看起来像我转换为数字的第二个查询,一个不同的值传递给我的函数,但我测试了排序列(索引),它只包含唯一的bigints.

这是test_dolf函数的代码：

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) > 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,' ');
       return arrDegMinSec[3];
    End 
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;