PostgreSQL Create Function

PostgreSQL functions are stored procedures that allow you to store a set of database operations in a function which can be called repeatedly without manually typing instructions every time. It is a great utility for database reuse and automation in applications. You can also use it to create user-defined functions and stored procedures. Here’s how to use PostgreSQL create function command.

PostgreSQL Create Function

Here’s the syntax of PostgreSQL CREATE FUNCTION.

CREATE [OR REPLACE] FUNCTION function_name (arguments) 
RETURNS return_datatype AS $variable_name$ 
DECLARE declaration; [...] 
BEGIN < function_body > [...] 
RETURN { variable_name | value } 
END; 
LANGUAGE plpgsql;

In the above query, you need to specify

function_name – name of the function

[OR REPLACE] – optional keyword for modifying existing function

function_body – set of operations to be executed as part of function

return statement with or without a return variable/value

declare variables to store function values

language – PostgreSQL allows you to define function in various language such as PostgreSQL, SQL, C, Python and other procedural languages.

Bonus Read : PostgreSQL Create Schema

CREATE FUNCTION example

Here’s an example of how to create stored procedure in PostgreSQL. Let’s say you have the following table sales

postgres=# select * from sales;
 order_date | sale
------------+------
 2020-04-01 |  210
 2020-04-02 |  125
 2020-04-03 |  150
 2020-04-04 |  230
 2020-04-05 |  200
 2020-04-10 |  220
 2020-04-06 |  250
 2020-04-07 |  215
 2020-04-08 |  300
 2020-04-09 |  250

Bonus Read : PostgreSQL Create Database

Let us create a total() function that counts the number of rows in sales table

postgres=# CREATE OR REPLACE FUNCTION total()
           RETURNS integer AS $total$
           declare
             total integer;
           BEGIN
             SELECT count(*) into total FROM sales;
             RETURN total;
          END;
          $total$ LANGUAGE plpgsql;
CREATE FUNCTION

In the above query, we have defined function total() such that it executes the SQL query mentioned between BEGIN and END and stores the result in declared variable $total , which is returned after function execution.

Here’s how to execute PostgreSQL function

postgres=# select total();
 total
-------
    10

Bonus Read : How to Create Histogram in PostgreSQL

Hopefully, the above tutorial will help you create user-defined function and stored procedure in PostgreSQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!