Demystifying Functions and Procedures in POSTGRESQL

POSTGRESQL Functions and Procedures

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();

The pg_proc is a system catalog table in PostgreSQL that stores information about stored procedures and functions. It contains details about user-defined functions and procedures, such as their names, argument types, return types, and other relevant information.

The smallints are the problem. A cast from integer to smallint can mean losing some information. The engine won’t do implicit casts, where information can get lost.

Implicit Type Cast:

• Automatic Conversion: PostgreSQL can automatically convert one data type to another when it is necessary and safe to do so

Explicit Type Cast:

• Manual Conversion: You explicitly specify the conversion in your SQL statements using the CAST function or the :: syntax.

FUNCTION

A Function can return 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.

Hope you understand the concept of Procedures and its uses and functions as well.

Happy Learning!!!!!