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?