CODEWITHSUNDEEP
sqlpostgresqlpostgresql-10
Ulysse BN
Ulysse BN

Reputation: 11414

How to clamp a float in PostgreSQL

I have a number 1.00000001, and I want to clamp it between -1 and 1 to avoid input out of range error on ACOS() function. An MCVE look like this:

SELECT ACOS( 1 + 0.0000000001 );

My ideal would be something like:

SELECT ACOS( CLAMP(1 + 0.0000000001, -1, 1) );

Upvotes: 3

Views: 3551

Answers (3)

Ulysse BN
Ulysse BN

Reputation: 11414

Following PG documentation, we can create the simple function below:

#include "postgres.h"
include "fmgr.h"

PG_MODULE_MAGIC;

PG_MODULE_MAGIC;

/* by value */

PG_FUNCTION_INFO_V1(clamp);

Datum
clamp(PG_FUNCTION_ARGS)
{
    int32 num = PG_GETARG_INT32(0);
    int32 min = PG_GETARG_INT32(1);
    int32 max = PG_GETARG_INT32(2);

    PG_RETURN_INT32(num < min ? min : (num > max ? max : num));
}

And then compile it and move it to the package lib directory (done on a Mac):

cc -I$(pg_config --includedir-server)  -c pg_clamp.c
cc -bundle -flat_namespace -o pg_clamp.so pg_clamp.o
mv pg_clamp.so $(pg_config --pkglibdir)
rm pg_clamp.o

Finally, reference it in SQL:

CREATE OR REPLACE FUNCTION clamp(int, int, int) RETURNS int AS 'pg_clamp', 'clamp' LANGUAGE C STRICT;

Upvotes: 0

srghma
srghma

Reputation: 5353

-- example: clamp(subject, min, max)
CREATE FUNCTION clamp(integer, integer, integer) RETURNS integer
    AS 'select GREATEST($2, LEAST($3, $1));'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

-- example: clamp_above(subject, max)
CREATE FUNCTION clamp_above(integer, integer) RETURNS integer
    AS 'select LEAST($1, $2);'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

-- example: clamp_below(subject, min)
CREATE FUNCTION clamp_below(integer, integer) RETURNS integer
    AS 'select GREATEST($1, $2);'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Upvotes: 3

Ulysse BN
Ulysse BN

Reputation: 11414

The solution I found was:

SELECT ACOS(GREATEST(-1, LEAST(1, 1 + 0.0000000001));

Upvotes: 7

Related Questions