top of page
hand-businesswoman-touching-hand-artificial-intelligence-meaning-technology-connection-go-

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:


  1. Ease of Use: Once we create a Stored Procedure, we can use it over and over without needing to rewrite the commands.

  2. Speed: Stored Procedures are already prepared, so the database can run them faster.

  3. 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.

  4. Reusability: We can use a Stored Procedure in many parts of our program.

  5. Security: Stored Procedures can help protect our data by controlling who can access it.

Disadvantages of Stored Procedures:


  1. Hard to Find Errors: If there’s a mistake in a Stored Procedure, it can be hard to figure out what went wrong.

  2. Dependent on Database: Stored Procedures are tied to the specific database system we’re using.

  3. Not Easy to Move: If we want to switch to a different database system, we might have to rewrite our Stored Procedures.

  4. 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:

  1. Open PostgreSQL (pgAdmin 4).

  2. Navigate to the ‘covid_details’ table in the COVID database.

  3. 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.


63 views0 comments

Recent Posts

See All

Comentarios

Obtuvo 0 de 5 estrellas.
Aún no hay calificaciones

Agrega una calificación
bottom of page