PostgreSQL functions

PostgreSQL has a lot of built-in functions, such as Trigonometric Functions, String Functions, IP address functions, etc. But we're not limited by built-in functions. PostgreSQL (as well as other RDBMS) allows to create custom functions. In this tutorial we will take a look at user-defined functions.

PostgreSQL supports the following programming languages for writing functions:

  • SQL
  • PL/pgSQL
  • C
  • PL/Python
  • PL/Tcl
  • some other languages

Create functions with PL/pgSQL

Functions written in PL/pgSQL can contain a variable declaration, conditional and looping constructions, exception handling and so on.
PL/pgSQL is a block-structured and case insensitive language.

Base syntax for creating functions in PostgreSQL looks into the following way:

CREATE OR REPLACE FUNCTION function_name (arguments)
RETURNS r_type AS $$
DECLARE
    declarations
BEGIN
    statements
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION - creates a new function named by function_name in case when such function doesn't exists, otherwise replaces the existing function.
arguments - is a list of function's arguments. Functions can have zero or more parameters(arguments). We may specify only parameter type, e.g. (int4, varchar(40)). In that case within a function body, the parameters of the function are referred to as $1, $2,.. and so on, in the order they are defined. But we also can specify parameters names together with its types (price int4, name varchar(40)).
RETURNS - returned data type from the function. In our case r_type is specified type which will be returned from the function.
DECLARE declarations; - is a declaration section, which contains the variables names and its data types.
BEGIN Statement; - is a statement section which is the main body of the function. It may contain unlimited nested sub-blocks (with DECLARE, BEGIN, END sections).
END - finishes the statement block.

BEGIN/END sections doesn't start or stop transactions. It's used for grouping blocks of code.

Function example

Let's assume we have a table vehicle
vehicle table
which has the following columns:

vehicle_id - vehicle's ID
make - vehicle's make
model - vehicle's model
price - vehicle's price in $

Now let's create a function which accepts desired price as a parameter and prints the amount of vehicles with price lower than or equals to desired price:

CREATE OR REPLACE FUNCTION getCheapVehicleCount(desiredPrice INTEGER)
	RETURNS INTEGER AS $$
	DECLARE
		total INTEGER;
	BEGIN
		SELECT count(*) INTO total FROM vehicle v WHERE v.price <= desiredPrice;
		RETURN total;
	END;
	$$ LANGUAGE plpgsql;

Now let's create a simple table vehicle:

DROP TABLE IF EXISTS vehicle;

CREATE TABLE vehicle (
	vehicle_id SERIAL NOT NULL,
	make VARCHAR(100) NOT NULL,
	model VARCHAR(100) NOT NULL,
	price NUMERIC(5) NOT NULL
);

and fill in it with the test data:

INSERT INTO vehicle (make, model, price) VALUES ('Toyota', 'Camry', 25560);
INSERT INTO vehicle (make, model, price) VALUES ('Audi', 'A4', 42825);
INSERT INTO vehicle (make, model, price) VALUES ('Acura', 'ILX', 30840);
INSERT INTO vehicle (make, model, price) VALUES ('BMW', '5 Series ', 67470);

Verifying function

For verification let's find how many vehicles have price less or equals to 30000$:

SELECT getCheapVehicleCount(30000);

The output will look into the following way:
cheap vehicle amount

Return multiple rows from the function

Let's assume we need a function which returns a list of vehicles that have price lower than or equals to desired price. Such function will look into the following way:

CREATE OR REPLACE FUNCTION getCheapVehicles(desiredPrice INTEGER)
	RETURNS TABLE (id int, make VARCHAR(100), model VARCHAR(100), price NUMERIC(5))  AS $$

BEGIN
	RETURN QUERY SELECT * FROM vehicle v WHERE v.price <= desiredPrice;
END;
$$ LANGUAGE plpgsql;

If we call the function getCheapVehicles into the following way:

SELECT * FROM getCheapVehicles(40000);

We'll get the following output:
cheap vehicles

In case when we call getCheapVehicles function without * FROM we'll get the following output:
cheap vehicles

PERFORM

When we call a function which doesn't return any result or we don't need the returned result we may discard result by using PERFORM keyword:

PERFORM query;

More detailed information about functions in PostgreSQL could be found in official documentation here: CREATE FUNCTION.

Alexey Bogdanov

Read more posts by this author.

Subscribe to Development notes

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!