ERROR: ungültige Eingabesyntax für Typ numeric

Hey All,

es handelt sich um bestehenden Code der plötzlich auf meinem Server mit PostgreSQL-Version 10.21 nicht mehr läuft. Auf dem Entwicklungssystem mit PostgreSQL-Version 12.15 funktioniert der Code.

Der Fehler liegt in der WHERE Klausel.

DROP FUNCTION IF EXISTS SucheNummer( text );
DROP FUNCTION IF EXISTS kuerze( INT, TEXT );

CREATE OR REPLACE FUNCTION kuerze( INT, TEXT )
    RETURNS text AS $$
    -- Kürzt die Telefonummer von rechts auf n Stellen
    -- (0049, +49, 0)-Problematik
    DECLARE
        laenge INT;
    BEGIN
          laenge = length( $2 );
          IF laenge <= $1 THEN
              RETURN $2;
          ELSE
               RETURN substring( $2, laenge  - $1 + 1 );
          END IF;
END; $$ LANGUAGE 'plpgsql';;;


CREATE OR REPLACE FUNCTION SucheNummer( text )
     RETURNS record AS $$
    DECLARE
         telnum ALIAS FOR $1;
          myname text;
          result record;
          format text;
    BEGIN
        format := '99999999999999999';
        IF telnum !~ '[0-9]' THEN
            SELECT INTO result 0 AS id, telnum AS name, 'Y'::char AS typ;
            return result;
    END IF;
    SELECT INTO result id, name::text, 'C'::char AS typ FROM (SELECT id, name, to_number(phone, format)::char(16) AS p, to_number(phone, format)::char(16) AS f, char_length(to_number(phone, format)::char(16)) AS l, char_length(to_number(phone, format)::char(16)) AS l1, char_length(to_number(telnum, format)::char(16)) AS lt FROM customer WHERE phone !='') AS xyz WHERE kuerze(lt, xyz.p ) LIKE kuerze(l,to_number(telnum, format)::char(16))||'%';
    IF result.name != '' THEN return result; END IF;
    SELECT INTO result 0 AS id, telnum AS name, 'X'::char AS typ;
    return result;
END; $$ LANGUAGE 'plpgsql';;;


SELECT suchenummer( '01757810995');

Ist etwas mit dem Code nicht kompatibel zu Version 10.21?

Viele Grüße
Ronny

to_number(phone, format)::char(16) macht für mich wenig Sinn.

Wenn Du die Telefonnummer auf 16 Stellen bringen willst ohne führende Null, würde ich eher sowas machen:

 rpad(trim(leading '0' from phone), 16, ' ');

Damit entfällt auch das Problem, dass Du eine Zeichenkette in eine Zahl umwandeln musst um sie danach wieder in eine Zeichenkette umzuwandeln.

P.S.: man kann den Parametern einer Funktion auch Namen geben. Ein max_laenge liest sich dann auch gleich viel besser als ein $1

P.P.S: Der Ausdruck: char_length(to_number(phone, format)::char(16)) kann übrigens zu 16 vereinfacht werden

::char(16) hat nichts mit der Länge zu tun. Denke da besser an ::text

::char(16) hatte ich vor vielen Jahren nur aus Gründen des Speicherplatzes benutzt.

Das Problem ist nicht einfach die führende ‘0’ zu entfernen sonder zwei Strings zu vergleichen die folgende Formate haben können.
String in DB ‘0175-7880222’
String von Asterisk ‘+491757880222’ sollen verglichen werden und gleich sein.
In der DB kann natürlich auch ‘00491757880222’ stehen. usw…
Asterisk ruft die Funktion SucheNummer( ‘text’ ) auf.

Die Frage ist auch warum es so ja viele, viele Jahre funktionierte und auf dem Entwicklungsserver noch heute funktioniert. Der Fehler tritt ausschließlich im produktivem System auf.

Und warum werden dann die Strings vorher in Zahlen umgewandelt? Das macht überhaupt keinen Sinn für mich. Den String 0175-7880222 kann man sowieso nicht direkt in eine Zahl umwandeln - vermutlich ist das die Ursache für Deinen Fehler.

Um eine Telefonnummer zu normalisieren entfernt man alle führenden Nullen und alle Ländervorwahlen - dazu muss man aber letztendlich alle Ländervorwahlen kennen Da nach dem + oder 00 ja zwischen 1 und 4 Zeichen kommen könnten.

Wenn es ausschließlich deutsche Telefonnummern sind, dann würde ich das über ein regexp_replace machen um die Ländervorwahl bzw. führende Nullen zu entfernen:

regexp_replace(phone, '^[0+]*(49)*', '');

Das wird aber für 0175-7880222 nicht funktionieren. Da muss man dann nochmal ein replace machen:

replace(regexp_replace(phone, '^[0+]*(49)*', ''), '-', '')

alternativ, ist es vermutlich sicherer alles zu entfernen was keine Ziffer ist:

regexp_replace(regexp_replace(phone, '^[0+]*(49)*', ''), '[^0-9]', '')

Ich habe den Fehler gefunden. In der Spalte ‘phone’ stand ein in einer Zeile ein Name statt einer Telefonnummer.

Den Fehler kann man mit
SELECT to_number( 'name statt nummer', '99999999999999999' );
reproduzieren;
ERROR: invalid input syntax for type numeric: " "

um sowas zu vermeiden gibt es check-constraints :wink:

Wie gesagt: die Umwandlung in eine Zahl ist keine gute Idee wenn man nicht wirklich eine Zahl braucht.

Ja ich weiß. Das werde ich gleich mal einbauen…

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.