PostgreSQL Functions using PL/PGSQL

Like other programming languages PostgreSQL also support user-defined functions. User-defined functions can be written in SQL and C. It also supports procedural languages (PLs) PostgreSQL-pgSQL, Python, Perl, and Tcl. These four procedural languages are included in the stranded distribution of PostgreSQL. To define a functioning user must have privilege on the language.

The basic syntax to writing a function is,

CREATE or REPLACE FUNCTION function_name(parameters_list)
    RETURNS return_type
    LANGUAGE plpgsqlAssociation, Aggregation, Composition
    AS
$$
declare 
    -- define variables
begin
    -- define logic here
end
$$

• RETURNS define the return type of the function. The return type can be an integer, string, query, table columns, etc. You can specify the void if there is no return type. RETURNS can be skipped if the parameter list contains OUT or INOUT parameters.
• LANGUAGE defines the language in which function will be implemented.
• The dollar quoted string constant ($$) specifies the block.

Let define a users table,

DROP TABLE IF EXISTS users;
CREATE TABLE users(
 id INT GENERATED BY DEFAULT AS IDENTITY,
 name VARCHAR(50),
 gems DEC (10),
 PRIMARY KEY (id));

Populate the users table with the default number of gems,

INSERT INTO users (name, gems) VALUES ('David', 1000); 
INSERT INTO users (name, gems) VALUES ('John', 2000);
INSERT INTO users (name, gems) VALUES ('Joe', 3000);

Now, let's write a function that will return the number of users with a specified number of gems

CREATE or REPLACE FUNCTION users_count(number_of_gems int)
    RETURNS int
    LANGUAGE plpgsql
    AS
$$
DECLARE 
    user_count integer;
BEGIN
    SELECT count(*) INTO user_count
    FROM users WHERE gems > number_of_gems;
    
    RETURN user_count;
END;
$$;

After creating the function, call it with a simple SELECT statement and pass the number of gems as per your requirement.

SELECT users_count(1500);

It will return the number of users who own gems to more than 1500.

The above function returns the integer value from the function. To return the table from function use the following return statement inside the function,

RETURNS table (column_list)

The column_list contains specified columns from the list. Let’s write a function that will return the user's id, name, and gems.

CREATE or REPLACE FUNCTION users_gems()
    RETURNS table (id int, 
          name varchar,
          gems DEC (10))
    LANGUAGE plpgsql
    AS
$$
BEGIN
    RETURN query 
    SELECT * FROM users;
    
END;
$$;

Now, get the output of the above function

SELECT users_gems();

The function accepts three types of parameters IN, OUT, and INOUT. The users_count() function has IN parameter and it is the default type. The OUT and INOUT parameters should be explicitly mentioned like,

OUT parameter type
INOUT parameter type 

OUT parameter returns the output from the function and does not need to mention the RETURNS type and return statement at the end of the block.

CREATE or REPLACE FUNCTION total_user_count(OUT user_count int)
    LANGUAGE plpgsql
    AS
$$
BEGIN
    SELECT count(*) INTO user_count
    FROM users;
    
END;
$$;

To get the output,Association, Aggregation, Composition

SELECT total_user_count();

The INOUT parameter can accept the value from the caller and will return the updated value.

PostgreSQL also supports function overloading. Function overloading means, function names are the same but have different arguments. When an overloaded function is called PostgreSQL select the best possible function based on the arguments.

To enlist all the user-defined functions with details use the command.

\df 

The output will show the schema, name, return type, and arguments list of a function.

To drop a function use the following statement

DROP FUNCTION function_name(argument_type) [ CASCADE | RESTRICT ];

If the function accepts any argument, you need to explicitly mention the argument type because of function overloading.