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/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
