This postgres function should return a decimal number, but instead the result is an integer. The SQL on its own works properly. What is wrong with the function structure? An example input is a degree minute second longitude in text - '1012104.00E'
CREATE OR REPLACE FUNCTION getSEC(inORD VARCHAR)
RETURNS NUMERIC(10) AS $$
DECLARE
sec NUMERIC(10);
BEGIN
sec := cast(right(substring(inOrd,0,length(inOrd)-3,2)as numeric(10))/3600;
RETURN sec;
END; $$
LANGUAGE plpgsql;
Thanks in advance.
numeric takes both a "precision" and "scale". Precision is the total number of digits. Scale is how many of those can be to the right of the decimal. The Postgres docs provide an example.
The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.
If you don't provide a scale, it defaults to 0 which means you get an integer. Thus numeric(10) is an integer with a max of 10 digits. The Postgres docs find this "feature" of the SQL standard particularly useless.
(The SQL standard requires a default scale of 0, i.e., coercion to integer precision. We find this a bit useless. If you're concerned about portability, always specify the precision and scale explicitly.)
If you want 1012104.00 you'd need numeric(9, 2). 9 digits, 2 of which are to the right of the decimal.
numeric(10) is an integer - if you look at the docs, it takes a second option (scale) that affects how many decimal places it takes, and defaults to 0.
https://www.postgresql.org/docs/current/static/datatype-numeric.html
You could cast to decimal instead:
CREATE OR REPLACE FUNCTION getSEC(inORD VARCHAR)
RETURNS NUMERIC(10) AS $$
DECLARE
sec NUMERIC(10);
BEGIN
sec := cast(right(substring(inOrd,0,length(inOrd)-3,2)as numeric(10))/3600;
RETURN sec;
END; $$
LANGUAGE plpgsql;
