PostgreSQL-How to Add a new column and Update it in same stored procedure

Before jumping into the topic let’s revise the basics of Stored Procedure like definition, advantages, disadvantages, syntax etc.

Definition of Stored Procedure:

A stored procedure in PostgreSQL is a set of SQL statements that can be stored in the database.These are pre-compiled objects which are compiled for the first time and their compiled format is saved, which executes (compiled code) whenever it is called.

The main reason for using Stored Procedures is to make it easier to work with databases. They help to make database operations, like getting or changing data, faster and more efficient.

Advantages of Stored Procedures:

Ease of Use: Once we create a Stored Procedure, we can use it over and over without needing to rewrite the commands.
Speed: Stored Procedures are already prepared, so the database can run them faster.
Less Data Sent Over Network: Because the commands are stored in the database, only the results need to be sent over the network, which can make things faster.
Reusability: We can use a Stored Procedure in many parts of our program.
Security: Stored Procedures can help protect our data by controlling who can access it.

Disadvantages of Stored Procedures:

Hard to Find Errors: If there’s a mistake in a Stored Procedure, it can be hard to figure out what went wrong.
Dependent on Database: Stored Procedures are tied to the specific database system we’re using.
Not Easy to Move: If we want to switch to a different database system, we might have to rewrite our Stored Procedures.
Costly: Creating and maintaining Stored Procedures can require skilled developers, which can be expensive.

PostgreSQL Stored Procedure Syntax:

CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(PARAMETER_NAME, DATATYPE)
LANGUAGE plpgsql
AS $$
DECLARE
 - variable declaration
BEGIN
 - PROCEDURE body - all logics
END;
$$

Where:

LANGUAGE : PostgreSQL supports writing stored procedures in several different languages.

Plpgsql : Stands for Procedural Language PostgreSQL

$$ : To replace multiple single quotation marks and print as it is whatever we write between $$ $$ symbols.

DECLARE: Where we can declare all your variables.

BEGIN: Inside BEGIN we can write procedure body and logic

Calling a stored procedure:

To call a stored procedure, we can use the CALL statement as follows:

Call stored_procedure_name(argument_list);

Now, let’s learn how to create a Stored Procedure for adding a new column to an existing table and updating it with specific criteria.

To make things clearer, let’s imagine we’re working with a table called ‘covid_details’ in a database focused on COVID information. This table already has 11 columns. We want to add a new column called ‘year_extracted’, and this column will be updated with the year extracted from the existing ‘date_of_onset’ column.

Step #1:

Before adding the new column to the ‘covid_details’ table, let’s first check all the existing columns. We can do this in two ways.

Option #1:

Open PostgreSQL (pgAdmin 4).
Navigate to the ‘covid_details’ table in the COVID database.
We’ll see all 11 columns listed there.

Option #2:

We can see all the columns by executing the following SELECT query in the pgAdmin 4 query tool:

Step #2:

Creating a stored procedure ‘pr_add_year_column()’ that alters the table covid_details to add the new column ‘year_extracted’.Now the ‘year_extracted’ column updated by using a Date Function ‘EXTRACT()’ to populate with the Year value from ‘date_of_onset’ column.

Let’s understand how the ‘EXTRACT()’ function works:

The ‘EXTRACT()’ function is a Date Function in PostgreSQL used for dealing with dates and times. It helps us pull out specific parts, like the day, month, minute, hour, etc., from a date or time value. We give it a ‘source’ (the date or time we want to extract from) and a ‘field’ (what we want to extract), and it gives us back that specific part. The result it gives is a number, usually a whole number.

Here’s the syntax for using the ‘EXTRACT()’ function:

EXTRACT(field FROM source)

The PostgreSQL EXTRACT() function requires two arguments: field and source

1) field

The field argument specifies which information we want to extract from the date/time value.

The following table illustrates the valid field values:

Field Value	TIMESTAMP	Interval
CENTURY	The century	The number of centuries
DAY	The day of the month (1-31)	The number of days
DECADE	The decade that is the year field divided by 10
DOW	The day of the week (Sunday (0), Monday (1) … Saturday (6)).	N/A
DOY	The day of the year (1-365/366)	N/A
EPOCH	The number of seconds since 1970-01-01 00:00:00 UTC	The total number of seconds in the interval
HOUR	The hour (0-23)	The number of hours
ISODOW	The day of the week, Monday (1) to Sunday (7)	N/A
ISOYEAR	The ISO 8601 week number of year	N/A
MICROSECONDS	The second field, including fractional parts, multiplied by 1000000	Sames as TIMESTAMP
MILLENNIUM	The millennium	The number of millennium
MILLISECONDS	The second field, including fractional parts, multiplied by 1000	Sames as TIMESTAMP
MINUTE	The minute (0-59)	The number of minutes
MONTH	The month 1-12	The number of months, modulo (0-11)
QUARTER	The quarter of the year (1 – 4)	The number of quarters
SECOND	The second field, including any fractional seconds	The number of seconds
TIMEZONE	The timezone offset from UTC, measured in seconds	N/A
TIMEZONE_HOUR	The hour component of the time zone offset	N/A
TIMEZONE_MINUTE	The minute component of the time zone offset	N/A
WEEK	The number of the ISO 8601 week-numbering week of the year	N/A
YEAR	The year	Sames as TIMESTAMP

2) source

The source is a value of type TIMESTAMP or INTERVAL. If you pass a DATE value, the function will cast it to a TIMESTAMP value.

For instance, if the value of ‘date_of_onset’ is ‘2022–04–29’, then the new column ‘year_extracted’ should store ‘2022’.

When we run the following query, the Stored Procedure ‘pr_add_year_column()’ should be created without any issues.

We can check that the Stored Procedure ‘pr_add_year_column’ has been successfully added by looking under the “Procedures” section.

Calling this Procedure ‘pr_add_year_column’

Step #3:

To make sure that the new column ‘year_extracted’ is added correctly and updated with only the year, we need to check it.

Either we can check in left panel of pgAdmin 4.

Or can execute a querry in Query Tool to check the added column and value of that column as expected.

So, in this way, we’re able to both add a new column and update it within the same stored procedure. I hope you found this explanation helpful in understanding Stored Procedures in PostgreSQL.

Thank you for taking the time to read this.