Re: float to numeric(7,3)

On 03/09/12 20:51, Steve Horn wrote:
> Have a very simple question, but cannot seem to find an answer anywhere.
>
> Using the ST_Distance function from PostGIS
> (http://www.postgis.org/docs/ST_Distance.html) which returns a float.
>
> I would like to return the result of this function rounded to 3 decimal
> places. What is the best way to do that?

Excellent question! I had some trouble with this recently myself...

shared=> select version();
version

-------------------------------------------------------------------------------------------------
PostgreSQL 9.0.4 on x86_64-unknown-openbsd5.0, compiled by GCC cc
(GCC) 4.2.1 20070719 , 64-bit
(1 row)

shared=> create function fl(int) returns float as $$ SELECT 3.0::float
$$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION
shared=> select gettype(fl(1));
ERROR: function gettype(double precision) does not exist
LINE 1: select gettype(fl(1));
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
shared=> select round(fl(1),2.0);
ERROR: function round(double precision, numeric) does not exist
LINE 1: select round(fl(1),2.0);
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
shared=> select round(fl(1),2.0::float);
ERROR: function round(double precision, double precision) does not exist
LINE 1: select round(fl(1),2.0::float);
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
shared=>

shared=> \df round
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-------+------------------+---------------------+--------
pg_catalog | round | double precision | double precision | normal
pg_catalog | round | numeric | numeric | normal
pg_catalog | round | numeric | numeric, integer | normal

*******

The error message indicates round(dp,dp)does not exist; yet '\df' says
there is. What is the correct syntax for this?