How to create a function in PostgreSQL?

@cortez.connelly 

To create a function in PostgreSQL, you can use the CREATE FUNCTION statement. Here is the general syntax:

1
2
3
4
5
6
7

CREATE FUNCTION function_name(parameter_list)
RETURNS return_type
AS $$
BEGIN
  -- function body goes here
END;
$$ LANGUAGE language_name;

Here is an example that creates a function that calculates the area of a rectangle given its length and width:

1
2
3
4
5
6
7

CREATE FUNCTION calc_rect_area(length float, width float)
RETURNS float
AS $$
BEGIN
  RETURN length * width;
END;
$$ LANGUAGE plpgsql;

This function can be called as follows:

1

SELECT calc_rect_area(2.5, 3.7);

This would return the result 9.25.

You can also specify the parameters as IN, OUT, or INOUT to indicate their direction (input, output, or both). For example:

1
2
3
4
5
6
7
8

CREATE FUNCTION swap(IN x int, INOUT y int)
RETURNS void
AS $$
BEGIN
  y := x;
  x := y;
END;
$$ LANGUAGE plpgsql;

This function can be called as follows:

1

SELECT swap(1, 2);

This would return no result, but the values of x and y would be swapped within the function.

I hope this helps! Let me know if you have any questions.