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!
