Introduction to PostgreSQL PL/pgSQL (original) (raw)

Last Updated : 23 Jul, 2025

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.