How to create a function in PostgreSQL?

by cortez.connelly , in category: PHP Databases , 9 months ago

How to create a function in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by cali_green , 3 months ago

@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.