Introduction to PostgreSQL PL/pgSQL

PostgreSQL is an open-source, strong and highly extensible object-relational database system. It combines the power of SQL with additional procedural features, making it ideal for handling complex workloads. In PostgreSQL, PL/pgSQL (Procedural Language/PostgreSQL) enhances the SQL functionality by enabling procedural programming for creating functions, triggers, and stored procedures.

This article provides an overview of PostgreSQL PL/pgSQL, including a detailed explanation of CRUD operations (Create, Read, Update, Delete) with examples, advantages, and disadvantages. This guide ensures we’re equipped to use PL/pgSQL for efficient database operations.

What is PostgreSQL PL/pgSQL?

PL/pgSQL is a procedural extension for PostgreSQL. It allows developers to combine SQL with procedural logic such as loops, conditions, variables, and control structures. This language is used to create:

• User-defined functions
• Stored procedures
• Triggers

Key Features

• Integration with SQL: Execute SQL commands seamlessly within procedural blocks.
• Flexibility: Supports custom logic for complex operations.
• Portability: Functions and triggers written in PL/pgSQL can be reused across multiple systems

Getting started with PostgreSQL PL/pgSQL

First We’ll learn how to create a single table using basic PLSQL commands. Then We’ll learn how to query data using basic selection techniques. Finally, we will learn how to update or delete the existing table structure. Let us start with the discussion as follows.

1. CREATE TABLE in PostgreSQL

The CREATE TABLE command will create a new, initially empty table in the database.

Syntax

CREATE TABLE table_name
(
column1 datatype(size), 
column2 datatype(size),...
columnN datatype(size)
);

Example

CREATE TABLE GFG
(
order_no int, 
about varchar(20),
fields text
);

Output

CREATE TABLE
Query returned successfully in 100ms.

2. INSERT INTO Command

The INSERT command is used to insert data into a table.

Syntax

INSERT INTO table_name 
values
(value1,value2,...value N);

Example

INSERT INTO GFG 
values
(1,'CSE portal','DBMS');

Output

INSERT 0 1
Query returned successfully in 57 msec.

3. SELECT query without WHERE keyword in PostgreSQL

The SELECT command when used without WHERE condition fetches all data from a table.

Syntax

SELECT * FROM table_name;

Example

SELECT * FROM GFG;

Output

Note:  The table has already been populated with the data using insert into command as discussed before.

SELECT query with WHERE keyword in PostgreSQL –

The SELECT command when used with WHERE condition fetches selected rows from a table.

Syntax

SELECT * FROM table_name 
WHERE condition;

Example

SELECT * FROM GFG 
WHERE fields='DBMS';

Output

4. UPDATE Command

The UPDATE command is used to make updates to the data or row(s) of a database table.

Syntax

UPDATE table_name 
SET column_name = NewValue 
WHERE condition;

Example

UPDATE GFG 
SET fields = 'Data analysis' 
WHERE order_no = 3;

Output

UPDATE 1
Query returned successfully in 65 msec.

To see the changes that have been successfully made after UPDATE command, run a SELECT command to display the whole table as follows:

5. DELETE Command

It is used to delete row(s) data from the table, WHERE clause condition is optional in DELETE query.

Syntax

DELETE FROM table_name 
WHERE condition;

Example

DELETE FROM GFG 
WHERE order_no = 4;

Output

DELETE 1
Query returned successfully in 61 msec.

To see the changes that have been successfully made after the DELETE command, run a SELECT command to display the whole table as follows.

Advantages of Using PL/pgSQL

• PL/SQL is tightly integrated with SQL i.e. PL/SQL lets us use all SQL data manipulation, and all SQL functions and operators.
• PL/SQL let us run SQL queries and process the rows of the result set one at a time.
• PL/SQL applications can be run on any operating system where there will be Oracle Database runs.
• It increases manageability because in this we can maintain only one copy of a subprogram and on the database server.
• It helps in increase scalability on the database server by centralizing application processing.

Disadvantages of using PL/pgSQL

• PL/pgSQL needs specialized skills that many developers do not possess and that’s why is it slower in the SDLC process.
• Difficult to manage versions and hard to debug.
• It may not be portable to other database management systems.

Conclusion

PL/pgSQL is a powerful procedural language for PostgreSQL that extends SQL functionality. It simplifies CRUD operations, enhances performance, and provides flexibility for complex database tasks. With its advantages in scalability and manageability, PL/pgSQL is an important tool for developers and database administrators.