How to optimise and call the function in PostgreSQL?

In PostgreSQL, a function is like a stored procedure that performs an operation. The best practice is to put common reusable logic in to the function.

There are two types of functions in PostgreSQL

• SQL Function: Quite straightforward. They execute simple queries with no logic or loops involved.

CREATE FUNCTION greet_world()
RETURNS TEXT AS $$
BEGIN
    RETURN 'Hello, World!';
END; $$ LANGUAGE plpgsql;

To call a SQL function

SELECT greet_world();

• PL/pgSQL Function: Allows complex operations includes conditional statements and loops.

CREATE FUNCTION greet_person(name TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN CONCAT('Hello, ', name);
END; $$ LANGUAGE plpgsql;

SELECT greet_person('John Doe');

How to optimise function calls in postgresql?

It is crucial to keep function as lean as possible and avoid put too much logic. More complex a function is, the longer it takes to process each call.

Next consider using SETOF instead of returning table types when returning function return types. Because, SETOF performs better than table and it allows the results to be returned as soon as they’re processed rather than waiting for all processes to be completed before sending them back.

CREATE OR REPLACE FUNCTION get_sales_details(customer_id int)
RETURNS SETOF sales AS
$BODY$
BEGIN
  RETURN QUERY SELECT id, sales_number FROM sales WHERE customerid = customer_id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

To call the function

SELECT * FROM get_sales_details(10);

Another optimisation strategy is Indexing. Indexing columns that are frequently in the WHERE and INNER JOIN clause will make a significant performance improvement.

If the we consider the above function, customerid column is used in the WHERE clause should be indexed to improve the performance. We can create the index as following.

CREATE INDEX idx_sales_customer_id on sales(customerid);

Hope it gives an idea on how to create a optimised functions and call it efficiently.

Happy SQL’ing 🙂