Open In App

Introduction to PostgreSQL PL/pgSQL

Improve
Improve
Like Article
Like
Save
Share
Report

In this, we will discuss the overview of PostgreSQL PL/pgSQL and will also cover the CRUD(CREATE, READ, UPDATE, DELETE) operations with the help of the example of each operation and finally will discuss the advantages and disadvantages of PostgreSQL PL/pgSQL. Let’s discuss it one by one.

PostgreSQL :
It is a powerful, open-source object-relational database system that extends the SQL language. It uses SQL language along with the combination of many features that safely scale the workloads. It is open-source and highly extensible. It also supports all major operating systems. It has a proven architecture along with several grounds like reliability, extensibility, data integrity that has been a major factor in the popularity of PostgreSQL. PostgreSQL is a procedural programming language. PL/pgSQL was designed to create user-defined functions, stored procedures, and triggers, inherit all user-defined functions, and types, and much more.

Getting started with PostgreSQL :
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’ll learn how to update or delete the existing table structure. Let us start with the discussion as follows.

CREATE TABLE query in PostgreSQL –
This 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.

INSERT INTO query in PostgreSQL –
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.

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 : 

order_no

(integer)

about

(character varying(20))

fields

(text)

1 CSE portal DBMS
2 Best Organization  Programming Languages
3 Find all solutions school learning 
4 easy to use GATE practice ques

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 :

order_no

(integer)

about

(character varying(20))

fields

(text)

1 CSE portal DBMS

UPDATE query in PostgreSQL –
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:

order_no

(integer)

about

(character varying(20))

fields

(text)

1 CSE portal DBMS
2 Best Organization  Programming Languages
4 easy to use GATE practice ques
3 Find all solutions Data analysis

DELETE query in PostgreSQL –
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.

order_no

(integer)

about

(character varying(20))

fields

(text)

1 CSE portal DBMS
2 Best Organization  Programming Languages
3 Find all solutions Data analysis

Advantages of using PL/pgSQL :

  • PL/SQL is tightly integrated with SQL i.e. PL/SQL lets you use all SQL data manipulation, and all SQL functions and operators.
  • PL/SQL lets you 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 you 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.

Last Updated : 17 Mar, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads