Stored Procedures in PostgreSQL

Contents :

• Stored Procedures in PostgreSQL
• Advantages of stored procedures
• Disadvantages of stored procedures
• View in SQL

Stored Procedures in PostgreSQL

PostgreSQL allows the user to extend the database functionality with the help of user-defined function and stored procedures through various procedural languages elements , which are often referred to as stored procedure. The Store procedures also add many procedural features. e.g. , Control structure and complex calculation. These allow you to develop custom function much easier and more effective.

It is possible to call a procedural code block using the Do command without defining a function or stored procedure

PostgreSQL categorizes the procedural languages into two main groups :

1. Safe language can be used by any users. SQL and PL/ PGSQL are safe language
2. Sand-boxed language are only used by superuser because sand-boxed language provide the capability to bypass security and allow access to external sources. C is an example of a sandboxed language. By default , PostgreSQL supports three procedural languages : SQL , PL / PGSQL and C . You can also load other procedural language e.g , perl , python and TCL into PostgreSQL using extensions.

Advantages of using PostgreSQL Stored Procedures :

The Stored Procedures bring many advantages as follows :

1. Reduce the number of rounds trips between application and database servers. All SQL statement are wrapped inside a function stored in the PostgreSQL database server so the application only has to issue a function stored in call to get the result back instead of sending multiple SQL statement and wait for the result between each call.
2. Increase application performance because the user-defined function and stored procedures are pre-complied and stored in the PostgreSQL database server
3. Reusable in many application. Once you develop a function , you can reuse it in any application

Disadvantages of using PostgreSQL Stored Procedures :

1. Slowness in software development because stored procedure programming requires specialized skills that many developers do not passes.
2. Difficult to manage version and hard to debug .
3. Many not be portable to other database management system , e.g , MySQL or Microsoft SQL server .

View in SQL

• Views in SQL are virtual table. A view also certain rowsand columns
• To create the view , we can select the fields from one or more table present in the database.
• A view can either have specific rows based on certain condition or all the rows of a table.
• Views restrict access to the data because the view can display selective columns and rows from the table.
• View provide groups of user with access to data according to their particular permission.
• Views can be used to retrieve data from several tables , providing data independence for user.

Syntax :

CREATE VIEW view_name AS

SELECT column1 , column2 ,…

FROM table_name

WHERE condition ;

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Thanks..

Visit for

1. Basic Concepts of PostgreSQL : https://medium.com/@haripriyapawar006/basic-concepts-of-postgresql-808db2606dd9
2. ORDER DATABASE : https://medium.com/@haripriyapawar006/order-database-3176d1d7cd99