POSTGRESQL Functions and Procedures

FUNCTIONS	PROCEDURES
COMMIT/ROLL BACK not allowed	COMMIT/ROLL BACK allowed
Returns Scalar/result set	Doesn’t return any values
Can be used in SELECT or DML statement	Can only be used by CALL statement

A procedure is a database object similar to a function. The key differences are:

• Procedures are defined with the CREATE PROCEDURE command.

• Procedures do not return a function value; hence CREATE PROCEDURE lacks a RETURNS clause. However, procedures can instead return data to their callers via output parameters.

• While a function is called as part of a query or DML command, a procedure is called in isolation using the CALL command.

• A procedure can commit or roll back transactions during its execution (then automatically beginning a new transaction), so long as the invoking CALL command is not part of an explicit transaction block. A function cannot do that.

When requirements are complex or need additional processing, procedures can be ideal. A complete programming language in their own right, and in this article native PostgreSQL PL/pgSQL. Most major databases include choices for programming languages so you can use whichever best fits your requirements; for a list/comparison see resources. With very close access to data they are incredibly fast. Closely tied to data, it can make sense investing in optimisation to process information through procedures, presenting output in a more easily digested format.

Highly optimised, stored procedures are usually used as discrete components, though they can be combined with other tables and views if required. Often used to generate specific reports or data sets, they are frequently used in single data panels or as a focused aspect to a report combined with other data.

Stored procedures have lots of benefits:

• Among other things they help decouple application code from the database tables, simplify database maintenance and versioning.

• you could use Stored procedures to  perform business logic, execute queries, do backups etc

• A stored procedure allows them to be executed with a single call. This minimizes the use of slow networks, reduces network traffic, and improves round-trip response time.

•  Productivity and Ease of Use. By designing applications around a common set of stored procedures, you can avoid redundant coding and increase the productivity

•  Once you execute a stored procedure, it remains in the cache, saving the execution time - so you get faster results.

• PostgreSQL Stored Procedures support procedural operations, which are helpful while building powerful database apps 

• Reduces NETWORK TRAFFIC-work directly within the data base

• Supports ENCAPSULATION— Storing business logic as an API in the database

• SECURITY— accèss rights can be granted to functions/procedures

• A subroutine available to applications that access a relational database management system

• Parameters in stored procedures can have the in and inout modes.

• They cannot have the out mode

• A stored procedure does not return a value. You cannot use the return statement with a value inside a store procedure like this:

• Stored procedure can be saved as file

• By using Stored procedures we can implement business logic that is reusable by multiple applications therefore we can achieve security and integrity of database.

• Whenever we want to involve the these statements we just call the stored procedure and execute it. Stored Procedure Syntax: create [or replace] procedure procedure_name(parameter_list) language plpgsql as $$ declare -- variable declaration begin -- stored procedure body end; $$ However, you can use the return statement without the expression to stop the stored procedure immediately: return; Here is the Sample Procedure using DVD Rental Database: CREATE  OR REPLACE PROCEDURE sel_cust_rent() language plpgsql as $$ declare v_customername varchar; v_storeid int; begin  select    concat(first_name, '', last_name),    store_id into v_customername,    v_storeid  from    customer,    rental  where    store_id = 2; raise notice 'this is store 2'; end $$ Calling a stored procedure,Syntax: call stored_procedure_name(argument_list); EXAMPLE  call sel_cust_rent(); The drop procedure statement deletes one or more stored procedures from a database drop procedure [if exists] procedure_name (argument_list) Example Drop sel_cust_rent(); FUNCTION Function can return a value and cannot modify the data received as parameters. Function cannot change anything and must have at least one parameter. Also, it must return a result. Stored procedures take no parameters, can modify database objects, and need not return results.  syntax of the create function statement: create [or replace] function function_name(param_list)    returns return_type     language plpgsql   as $$ declare  -- variable declaration begin  -- logic end; $$ Example: CREATE FUNCTION check_staff_user()     RETURNS TRIGGER AS $$ BEGIN     IF length(NEW.username) < 8 OR NEW.username IS NULL THEN         RAISE EXCEPTION 'The username cannot be less than 8 characters';     END IF;     IF NEW.NAME IS NULL THEN         RAISE EXCEPTION 'Username cannot be NULL';     END IF;     RETURN NEW; END; $$ LANGUAGE plpgsql; In language section ,PostgreSQL supports many procedural languages, not just plpgsql. PL/pgSQL supports three parameter modes: in, out and inout. By default, a parameter takes the in mode.

• First, specify the name of the stored procedure after the create procedure keywords.

• Second, define parameters for the stored procedure. A stored procedure can accept zero or more parameters.

• Third, specify plpgsql as the procedural language for the stored procedure. Note that you can use other procedural languages for the stored procedure such as SQL, C, etc.

• Finally, use the dollar-quoted string constant syntax to define the body of the stored procedure.