PostgreSQL Create Function

In PostgreSQL, the CREATE FUNCTION statement is used to create user-defined functions. Functions in PostgreSQL allow you to encapsulate a set of SQL statements and logic into a single reusable unit, which can be called from other SQL queries or used as stored procedures.

Syntax

The basic syntax for creating a function in PostgreSQL is as follows:

CREATE FUNCTION function_name ([argument1 data_type [, argument2 data_type, ...]])
  RETURNS return_type
  LANGUAGE language_name
  [DETERMINISTIC | NOT DETERMINISTIC]
  [SQL DATA ACCESS {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}]
  [COMMENT 'string']
  [SET configuration_parameter {TO value | = value | = DEFAULT}]
AS
$$
  -- Function body with SQL statements and logic
$$;

Let’s break down the components of this syntax:

CREATE FUNCTION: This is the statement that initiates the creation of a function.
function_name: The name you want to assign to your function.
[argument1 data_type [, argument2 data_type, …]]: The optional list of input arguments that the function accepts. Each argument consists of a name and a data type.
RETURNS return_type: The data type that the function returns.
LANGUAGE language_name: Specifies the language to be used for writing the function. In PostgreSQL, you can use different languages such as SQL, PL/pgSQL, Python, etc.
[DETERMINISTIC | NOT DETERMINISTIC]: Indicates whether the function always produces the same result for the same input. This is an optional clause.
[SQL DATA ACCESS {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}]: Specifies the type of SQL data access the function requires. This is an optional clause.
[COMMENT ‘string’]: An optional comment that provides a description of the function.
[SET configuration_parameter {TO value | = value | = DEFAULT}]: Allows you to set specific configuration parameters for the function.
AS: Begins the function body section.
$$: Delimiters that enclose the function body. You can use different delimiters if necessary.
— Function body with SQL statements and logic: The actual SQL statements and logic that define the behavior of the function.
$$: The closing delimiter for the function body.

Example

Here’s a simple example that demonstrates the creation of a function in PostgreSQL:

CREATE FUNCTION calculate_average(a integer, b integer)
  RETURNS float
  LANGUAGE SQL
AS
$$
  SELECT (a + b) / 2.0;
$$;

In this example, we create a function called “calculate_average” that accepts two integer arguments and returns a float value. The function body consists of a single SQL statement that calculates the average of the two input values.

Once the function is created, you can call it from other SQL queries using its name, just like any built-in PostgreSQL function. For example:

SELECT calculate_average(10, 20); 
-- Returns 15.0

This executes the “calculate_average” function with arguments 10 and 20 and returns the result 15.0.

Create Function example

CREATE OR REPLACE FUNCTION
change_price(in p_name varchar(50), in p_price numeric) 
RETURNS varchar AS $$
DECLARE
  v_msg varchar(250):='Price changed';
  v_count numeric:=0;
BEGIN
  SELECT count(*) into v_count 
  FROM goods 
  WHERE name = p_name;
  if v_count=0 then
    v_msg:='No name found';
    return v_msg;
  else
    update goods 
	set price=p_price 
	where name = p_name;
    return v_msg;
  end if;   
EXCEPTION 
  when others then
    begin
     v_msg := 'Error!';
     return v_msg;
    end;    
END;
$$ LANGUAGE 'plpgsql';

PostgreSQL provides extensive support for creating functions with complex logic, error handling, and procedural languages like PL/pgSQL. The CREATE FUNCTION statement is a powerful tool that enables you to extend the functionality of the database and enhance your SQL queries with custom logic and calculations.