EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Variables

PostgreSQL Variables

Sohel Sayyad
Article bySohel Sayyad
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 12, 2023

PostgreSQL Variables

Introduction to PostgreSQL Variables

The PostgreSQL variable is a convenient name or an abstract name given to the memory location. The variable always has a particular data-type give to it, like boolean, text, char, integer, double precision, date, time, etc. They are used to store the data which can be changed. The PostgreSQL variables are initialized to the NULL value if they are not defined with a DEFAULT value. We can modify the value stored within the variable by using the function or code block. We can store the data temporarily in the variable during the function execution.

ADVERTISEMENT
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

Consider the following syntax to declare a variable:

DECLARE var_name [ CONSTANT ] data-type [ NOT NULL ] [ { DEFAULT | := } initial_value ]

Explanation:

  • var_name: The variable name to assign.
  • CONSTANT: This is an optional component. If we have defined the CONSTANT, we can not change the variable’s value once the variable has been initialized.
  • data-type: The variable data-type to assign.
  • NOT NULL: This is an optional component. If we have defined the NOT NULL, then the variable can not have a NULL value.
  • initial_value: This is an optional component. By using this, we can initialize the variable while creating the variable. If we have not defined the initial_value, then the variable will be assigned with the NULL value.

How to Initialize Variables in PostgreSQL?

There are various ways to initialize the variables that are given as follows:

1. While the creation

We can initialize the variable while creating the variable by giving an initial value.

Consider the following example to understand the variable initialization.

Code:

DECLARE num_of_students integer := 100;

or

DECLARE num_of_students integer DEFAULT 100;

The above example would declare a PostgreSQL variable of name num_of_students having initial_value as 100 and data-type as an integer.

2. After creation

We can declare a variable first, and then we can initialize the variable.

Consider the following example to understand the variable initialization after creation.

Code:

DECLARE num_of_students integer;

The above example would declare a PostgreSQL variable of name num_of_students having data-type as an integer.

Now we will initialize the variable by using the following statement:

Code:

num_of_students := 300

The above statement would initialize a PostgreSQL variable of name num_of_students with a value of 300.

How to Declare Variables in PostgreSQL?

There are various ways to declare the variable that is given as follows:

1. DECLARE with initial_value

Consider the following example to understand the variable declaration with initial_value.

Code:

DECLARE name_of_student VARCHAR:= 'John';

or

DECLARE name_of_student VARCHAR DEFAULT 'John';

The above example would declare a PostgreSQL variable of name name_of_student having data-type as VARCHAR and initial_value as ‘John’.

2. DECLARE without initial_value

Consider the following example to understand the variable declaration without an initial value.

Code:

DECLARE name_of_student VARCHAR;

The above example would declare a PostgreSQL variable of name name_of_student having data-type as VARCHAR.

3. DECLARE CONSTANT variable

Consider the following example to understand the variable declaration with an initial value and as a CONSTANT.

Code:

DECLARE name_of_student CONSTANT VARCHAR:= 'John';

or

DECLARE name_of_student CONSTANT VARCHAR DEFAULT 'John';

The above example would declare a PostgreSQL variable of name name_of_student having data-type as VARCHAR and having an initial value as ‘John’, which will be changed further as it is specified as CONSTANT.

How do Variables work?

  • All of the PostgreSQL variables we use in the function must be defined within the DECLARE keyword.
  • During the execution of the function, we can temporarily store the data in the variable.
  • We can modify the data stored within the variable.
  • We cannot change the variable’s value if any of the PostgreSQL variables is defined as the CONSTANT.
  • If a PostgreSQL variable is not specified as CONSTANT, we can declare it with a default value and change it later as necessary.

Examples of PostgreSQL Variables

Given below are the examples:

Example #1

Gives initial value to a PostgreSQL variable.

a. Without DEFAULT keyword

Consider the following function of the name:

Code:

/*"Initial_val_without_default_keyword"()*/
-- FUNCTION: public."Initial_val_without_default_keyword"()
-- DROP FUNCTION public."Initial_val_without_default_keyword"();
CREATE OR REPLACE FUNCTION public."Initial_val_without_default_keyword"(
)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$DECLARE
def_var integer := 100;
BEGIN
RETURN def_var;
END;
$BODY$;

Now we will execute the above function.

Illustrate the following SQL statement and snapshot the result of the above function.

Code:

SELECT public."Initial_val_without_default_keyword"()

Output:

postgreSQL Variables 1

b. With default keyword

Consider the following function of the name:

Code:

"Initial_val_with_default_keyword"()
-- FUNCTION: public."Initial_val_with_default_keyword"()
-- DROP FUNCTION public."Initial_val_with_default_keyword"();
CREATE OR REPLACE FUNCTION public."Initial_val_with_default_keyword"(
)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$DECLARE
def_var integer default 200;
BEGIN
RETURN def_var;
END;
$BODY$;

Now we will execute the above function.

Illustrate the following SQL statement and snapshot the result of the above function.

Code:

SELECT public."Initial_val_with_default_keyword"()

Output:

With default keyword

c. CONSTANT variable
  • without DEFAULT keyword

Consider the following function of the name:

Code:

-- FUNCTION:
public."Initial_val_constant_without_default_keyword"()
-- DROP FUNCTION
public."Initial_val_constant_without_default_keyword"();
CREATE OR REPLACE FUNCTION
public."Initial_val_constant_without_default_keyword"(
)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$ DECLARE
def_var CONSTANT integer := 200;
BEGIN
RETURN def_var;
END;
$BODY$;

Now we will execute the above function.

Illustrate the following SQL statement and snapshot the result of the above function.

Code:

SELECT public."Initial_val_constant_without_default_keyword"()

Output:

without default keyword

  • With default keyword

Consider the following function of the name:

Code:

-- FUNCTION:
public."Initial_val_constant_with_default_keyword"()
-- DROP FUNCTION
public."Initial_val_constant_with_default_keyword"();
CREATE OR REPLACE FUNCTION
public."Initial_val_constant_with_default_keyword"(
)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$DECLARE
def_var CONSTANT integer default 300;
BEGIN
RETURN def_var;
END;
$BODY$;

Now we will execute the above function.

Illustrate the following SQL statement and snapshot to understand the result of the above function:

Code:

SELECT public."Initial_val_constant_with_default_keyword"()

Output:

postgreSQL Variables 3

Example #2

Gives a value to a PostgreSQL variable after declaration.

Consider the following function of the name:

Code:

-- FUNCTION: public."Initial_val_later"()
-- DROP FUNCTION public."Initial_val_later"();
CREATE OR REPLACE FUNCTION public."Initial_val_later"(
)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$Declare
def_var integer ;
BEGIN
def_var:=500;
RETURN def_var;
END;
$BODY$;

Now we will execute the above function.

Illustrate the following SQL statement and a snapshot of the above function:

Code:

SELECT public."Initial_val_later"()

Output:

postgreSQL Variables 4

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Variables” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL Triggers
  2. HAVING PostgreSQL
  3. PostgreSQL SPLIT_PART()
  4. PostgreSQL EXTRACT()
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
63+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW