A function to check for valid integers:
CREATE OR REPLACE FUNCTION retInt(VARCHAR) RETURNS integer AS '
DECLARE number ALIAS FOR $1; i INTEGER := 1;
BEGIN IF ((number IS NULL) OR (number = '''')) THEN RETURN NULL; END IF; WHILE (i <= length(number)) LOOP
IF ((substr(number,i,1) < ''0'') OR (substr(number,i,1)) > ''9'') THEN RETURN NULL; END IF; i := i +
1; END LOOP; RETURN to_number(number,''999999999999990'');
END;
' LANGUAGE plpgsql;
\pset null (NULL)
DROP TABLE test;
CREATE TABLE test (number VARCHAR);
INSERT INTO test VALUES('123');
INSERT INTO test VALUES('a123');
INSERT INTO test VALUES('123b');
INSERT INTO test VALUES('');
SELECT retInt(number) FROM test;
retint
-------- 123(NULL)(NULL)(NULL)
(4 lines)
_______________________________________________________
Yahoo! Acesso Grátis - Internet rápida e grátis.
Instale o discador agora! http://br.acesso.yahoo.com/
