Postgres Pro
Downloads
Home   >   mailing lists

Thread: abs value, power, sqrt

abs value, power, sqrt

From
Michael Olivier
Date:
Hi folks,

I previously posted this to pgsql-novice but haven't gotten an answer,
so I thought I'd try here.

I need a way to get abs value of a floating point number and (possibly
separate function for) abs value of an integer in a select statement.
Is there a function to do this? If not, how can I write a function to
do it?

Also how do I do squares and square roots? I saw some \|/ type notation
for square root in an SQL book, but I didn't get postgres to take that.

thanks,
Michael

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: [SQL] abs value, power, sqrt

From
Ulf Mehlig
Date:
Michael Olivier <molivier@yahoo.com> wrote:

> I need a way to get abs value of a floating point number and
> (possibly separate function for) abs value of an integer in a select
> statement.

You had to look up the manuals  at numerical *operators* -- no need to
define YAF ;-)

      => select @-17.0;
   17

> Also how do I do squares and square roots? I saw some \|/ type
> notation for square root in an SQL book, but I didn't get postgres
> to take that.

try e.g.

      => select 2*2, 2^2;
   4        4

and:
      => select |/2;
   1.4142135623731

without the backslash (originally quoting in a shell?) it seems to
work ...

Hope it helps
Ulf

--
======================================================================
 %%%%%            Ulf Mehlig              <umehlig@zmt.uni-bremen.de>
   %%%%!%%%       Projekt "MADAM"         <umehlig@uni-bremen.de>
%%%% %!% %%%%     ----------------------------------------------------
 ---| %%%         MADAM:  MAngrove    |  Center for Tropical Marine
    ||--%!%              Dynamics     |  Biology
    ||                  And           |  Fahrenheitstrasse 1
 _ /||\_/\_            Management     |
/  /    \  \ ~~~~~~~~~~~~~~~~~        |  28359 Bremen/Germany
  ~~~~~~~~~~~~~~~~~~~~

Re: [SQL] abs value, power, sqrt

From
Bob Smither
Date:
On Thu, 19 Nov 1998, Michael Olivier wrote:

:Hi folks,
:
:I previously posted this to pgsql-novice but haven't gotten an answer,
:so I thought I'd try here.
:
:I need a way to get abs value of a floating point number and (possibly
:separate function for) abs value of an integer in a select statement.
:Is there a function to do this? If not, how can I write a function to
:do it?
:
:Also how do I do squares and square roots? I saw some \|/ type notation
:for square root in an SQL book, but I didn't get postgres to take that.

On my RH installation I found:

  file:/usr/doc/postgresql-6.3.2/user/c05.htm

which I have attached.  It documents the operators and functions
available.  Examples of your specific operators are:

  absolute value: @-5
  square root   : |/ 25

Square would just be x * x or use the exponentiation operator:

  2.0 ^ 3.0

As an example, here is a snippet from a perl cgi script that finds all the
zip codes within a specified radius of a target location.  The target
location is specified by $lat and $lon (in degrees), the radius by
$Radius (in miles).

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
#! /usr/bin/perl -w
# file: zips.by.location.m4

snip ...

# radius is in miles ...
$Radius = $input{'radius'};
# convert radius to degrees, circumference is pi * d
$Radius = $Radius * 360.0 / (7900.0 * atan2(1,1) * 4.0);
$RadiusSquared = $Radius * $Radius;

snip ...

# call psql to find the zip codes ...
$temp = qx{ psql -d lrcf -c "
  select
    city, state, zip5,
    population, latitude, longitude
  from
    zips
  where
    ( ( ((latitude - $lat) ^ 2.0) +
       ((longitude - $lon) ^ 2.0) )
      < $RadiusSquared );" };

# print results to html page
print "<pre>$temp</pre>";

snip ...

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Hope this helps,
======================================================================
Bob Smither, Ph.D.      281-331-2744; fax:-4616      Smither@C-C-I.Com
Windows - making simple things easy, and interesting things impossible
======================================================================

Re[2]: [SQL] abs value, power, sqrt

From
Sferacarta Software
Date:
Hello Bob,

sabato, 21 novembre 98, you wrote:

BS> On Thu, 19 Nov 1998, Michael Olivier wrote:

BS> :Hi folks,
BS> :
BS> :I previously posted this to pgsql-novice but haven't gotten an answer,
BS> :so I thought I'd try here.
BS> :
BS> :I need a way to get abs value of a floating point number and (possibly
BS> :separate function for) abs value of an integer in a select statement.
BS> :Is there a function to do this? If not, how can I write a function to
BS> :do it?
BS> :
BS> :Also how do I do squares and square roots? I saw some \|/ type notation
BS> :for square root in an SQL book, but I didn't get postgres to take that.

------------------------------------------------------------------------
funct    PostgreSQL              result                remarks
------------------------------------------------------------------------
ABS:    select @ -12            12                    absolute value
MODULE: select 100 % 3          1                     module
POWER:  select dpow(12,2)       144                   power
ROUND:  select dround(12.7)     13                    round
SQRT:   select dsqrt(144)       12                    square root
TRUNC:  select dtrunc(1.2)      1                     truncate int value
ROOT3:  select dcbrt(27)        3                     cube root
LOG:    select dlog1(100)       4.60517018598809      logarithm
EXP:    select dexp(2)          7.38905609893065      exponent
------------------------------------------------------------------------



Jose'