2

I'm trying to write a function that takes a minimum and a maximum input and returns a double.

Inputs:

high (integer)
low (integer)

Output:

val (double)

My SQL code is:

CREATE OR REPLACE FUNCTION random_between(low INT ,high INT) 
   RETURNS DOUBLE AS

BEGIN
   RETURN floor(random()* (high-low + 1) + low);
END;

The error:

ERROR: syntax error at or near "BEGIN"

GMB
  • 216,147
  • 25
  • 84
  • 135
CodePenguin
  • 68
  • 6

1 Answers1

2

You could write this as pure SQL function, like so:

create or replace function random_between(low int ,high int) 
returns double precision as $$
    select floor(random()* (high-low + 1) + low);
$$ language sql;

Problems with your code:

  • the body of the function needs to be surrounded with single quotes (or something equivalent, such as $$)

  • there is no double datatype in Postgres; maybe you meant double precision; note, however, that this is an inexact datatype: this might, or might not be what you want, but make sure that you understand the implications

  • you need to specify the language of the function

GMB
  • 216,147
  • 25
  • 84
  • 135
  • it is the first time i create a function in sql, the function can be used for all the elements of a table? for example i have a table of 10 elements which have a `max` and a `min` column. i would execute my function for all the rows and store the values in a new column called `value` – CodePenguin Dec 01 '20 at 19:02