Spring Deals - hours only!Up to 70% off on all courses and bundles.-Close
Function management
2. CREATE OR REPLACE FUNCTION

Instruction

Great! Recall the get_pi() function you wrote in the first part of this course:

CREATE FUNCTION get_pi()
RETURNS decimal AS $$
BEGIN
  RETURN 3.14159265358979;
END;
$$ LANGUAGE PLpgSQL;

Now, imagine that we no longer need the fractional part of pi to have 14 digits but instead would like it to have four digits after the decimal point, like 3.1415.

That's where the CREATE OR REPLACE FUNCTION command comes into play. This command allows us to replace the body of an existing function with the same name and arguments.

CREATE OR REPLACE FUNCTION get_pi()
RETURNS decimal AS $$
BEGIN
   RETURN 3.1415;
END;
$$ LANGUAGE plpgsql;

If a function with the same name and arguments doesn't exist in our database, a new one will be created.

In PostgreSQL, it's very common to use the CREATE OR REPLACE FUNCTION command in scripts. This way, the function is created if it doesn't exist and replaced otherwise.

Exercise

Run the template query to make the get_pi() function return 3.1415.