plpgsql variable assignment

How to declare variables in PL/pgSQL stored procedures

Rajkumar Raghuwanshi

SUMMARY: This article covers how stored procedures can make use of variables to be more functional and useful. After defining PL/pgSQL, stored procedures, and variables, it provides examples of how variables can be used.

The title of this post makes use of 3 terms: PL/pgSQL, stored procedure, and variable. Let’s start with a basic understanding of them.

PL/pgSQL : An abbreviation for Procedure Language/PostgreSQL. It is a procedural language that provides the ability to perform more complex operations and computations than SQL.

Stored Procedure: A block for SQL statements combined together under a name and saved in database which can be called on multiple times when needed.

Variable: A variable holds a value that can be changed through the block. It is always associated with a datatype. 

Now let’s try to understand these with examples.

Stored procedures include functions, procedures, triggers, and other objects that can be saved in databases. Below is a simple example for a stored procedure “Procedure”:

In this example, an SQL statement, which upon call prints “Procedure example1 called,” is saved under the name example1 and can be called multiple times as needed.

The example has a fixed message which it prints upon call. To make the function more dynamic and useful, we can use different types of variables and assign values to them at compile time as well at run time.

A variable must be declared in the declaration section of the PL/pgSQL block. Declaration syntax for a variable is: “ variable_name data_type [:=value/constant/expression]; ”

Variable_name: This can be any meaningful name or whatever the user wants.

Data_type: PostgreSQL supports data types like integer, numeric, varchar, and text, or it can be a %TYPE or %ROWTYPE. Here is a list of PostgreSQL supported data types: https://www.postgresql.org/docs/current/datatype.html .

Variable Assignment: Any value as accepted by data type, constant, or expression can be assigned to the variable. This part is optional. 

The user can print variable values by using RAISE NOTICE/EXCEPTION and “%” as a placeholder to be replaced by the variable value.

Let’s see an example for variable declaration and display:

The variable can also be of a column type or a row type of a table. These can be declared with data type as %TYPE and %ROWTYPE. Here is an example:

In this example the data type of the variable “eid_var” is declared by reference to the “eid” column’s data type in the “emp” table As output the user wants to return a complete row (all columns) of the “emp” table, so the variable “result” is declared as a reference to a whole row type of the “emp” table.

Another point to notice is that the “result” variable is assigned at runtime by using the result set of SELECT * INTO.

Another way to use %ROWTYPE in PostgreSQL variables is using RECORD as the data type of a variable. Below is the same example as above, but displaying “emp” table data using RECORD type.

In the same way, the user can use variables in other stored procedures like function and triggers.

Reference Links:

https://www.postgresql.org/docs/current/datatype.html

https://www.postgresql.org/docs/current/plpgsql-declarations.html

https://www.postgresql.org/docs/current/sql-createprocedure.html

Popular Links

• Connecting PostgreSQL using psql and pgAdmin
• How to use PostgreSQL with Django
• 10 Examples of PostgreSQL Stored Procedures
• How to use PostgreSQL with Laravel
• How to use tables and column aliases...

Featured Links

• PostgreSQL vs. SQL Server (MSSQL)...
• The Complete Oracle to PostgreSQL Migration...
• PostgreSQL vs. MySQL: A 360-degree Comparison...
• PostgreSQL Replication and Automatic Failover...
• Postgres on Kubernetes or VMs: A Guide...
• Postgres Tutorials
• The EDB Blog
• White Papers
• The EDB Docs

Relevant Blogs

Edb tutorial: how to run a tpc-e-like benchmark easily, edb tutorial: how to use privilege analysis to identify [un]used privileges, edb tutorial: how to get the most out of edb query advisor.

EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes

EDB Tutorial: How To Run a Complex Postgres Benchmark Easily - Master TPC-C in 3 Short Steps

An overview of postgresql indexes.

Variable assignment is done with PL/pgSQL's assignment operator ( := ), in the form of left_variable := right_variable , in which the value of the right variable is assigned to the left variable. Also valid is left_variable := expression , which assigns the left-hand variable the value of the expression on the right side of the assignment operator.

Variables can be assigned default values within the declaration section of a PL/pgSQL code block. This is known as default value assignment , and is done by using the assignment operator ( := ) on the same line as the variable's declaration. This topic is discussed in more detail later in this section, but Example 11-14 provides a quick demonstration.

Example 11-14. Default value assignment

It is also possible to use a SELECT INTO statement to assign variables the results of queries. This use of SELECT INTO is different from the SQL command SELECT INTO , which assigns the results of a query to a new table.

Note: To assign the results of a query to a new table within PL/pgSQL, use the alternative SQL syntax CREATE TABLE AS SELECT ).

SELECT INTO is primarily used to assign row and record information to variables declared as %ROWTYPE or RECORD types. To use SELECT INTO with a normal variable, the variable in question must be the same type as the column you reference in the SQL SELECT statement provided. The syntax of SELECT INTO statement is shown in the following syntax:

In this syntax, target_variable is the name of a variable that is being populated with values, and select_clauses consists of any supported SQL SELECT clauses that would ordinarily follow the target column list in a SELECT statement.

Example 11-15 shows a simple function that demonstrates the use of a SELECT INTO statement. The ALIAS keyword is described in the Section called Argument Variables ," later in this chapter. See the Section called Controlling Program Flow " for examples of using SELECT INTO with RECORD and %ROWTYPE variables.

Example 11-15. Using the SELECT INTO statement

Example 11-16 shows the results of the get_customer_id() function when passed the arguments Jackson and Annie . The number returned is the correct ID number for Annie Jackson in the customers table.

Example 11-16. Result of the get_customer_id( ) function

If you wish to assign multiple column values to multiple variables, you may do so by using two comma-delimited groups of variable names and column names, separated from one another by white space. Example 11-17 creates essentially an inverse function to the get_customer_id() function created in Example 11-15 .

Example 11-17. Using SELECT INTO with multiple columns

Example 11-18 shows the results of the get_customer_name() function, when passed an argument of 107.

Example 11-18. Result of the get_customer_name( ) function

Use the special FOUND Boolean variable directly after a SELECT INTO statement to check whether or not the statement successfully inserted a value into the specified variable. You can also use ISNULL or IS NULL to find out if the specified variable is NULL after being selected into (in most situations, this would mean the SELECT INTO statement failed).

FOUND, IS NULL , and ISNULL should be used within a conditional ( IF/THEN ) statement. PL/pgSQL's conditional statements are detailed in the "Controlling Program Flow" section of this chapter. Example 11-19 is a basic demonstration of how the FOUND Boolean could be used with the get_customer_id() function.

Example 11-19. Using the FOUND boolean in get_customer_id( )

Example 11-20 shows that get_customer_id( ) now returns a –1 value when passed the name of a non-existent customer.

Example 11-20. Result of the new get_customer_id( ) function

PL/pgSQL SELECT INTO Statement - Assign Data to a Variable

• March 31, 2023, 5:11 p.m.

Talha Saif Malik

• OpenSource Postgres  

In PostgreSQL, the PL/pgSQL SELECT INTO statement helps us store the table's data into a specific variable. The PL/pgSQL SELECT INTO statement fetches the data from a particular table and assigns it to the given variable. Different clauses can be used with the PL/pgSQL SELECT INTO statement for different purposes, such as the WHERE clause, GROUP BY clause, JOIN, etc.

This article explains how to use the PL/pgSQL SELECT INTO command to copy data from the selected table into a specific variable.

Follow the below instructions to assign data from a table to a variable:

- Specify the SELECT statement followed by the select expression. - After that, utilize the INTO keyword followed by the variable name. - Finally, specify the table’s name in the FROM clause.

Here, is the basic syntax for the PL/pgSQL SELECT INTO statement:

Example 1: How to Use PL/pgSQL SELECT INTO Statement?

A sample table with the following records has been already created in the database:

Suppose we want to assign the total number of employees to a specific variable. For this, we will utilize the SELECT INTO statement, as follows:

In the above code:

- An integer variable named “emp_counter” is declared. - The COUNT(*) is used to count the rows that match the specified criteria. - The INTO keyword is used to specify the rows count into the “emp_counter” variable. - The FROM clause keeps the name of the targeted table, i.e., “emp_bio”. - The “RAISE NOTICE” is used to display the variable’s value.

The output shows that the total number of employees has been assigned to the emp_counter variable.

Example 2: How to Use PL/pgSQL SELECT INTO Statement With WHERE Clause?

In the following example, the WHERE clause is used with the SELECT INTO statement to copy the table’s data on specific criteria:

The clause will filter the employees based on their salary. Only those employees will be counted and assigned to the “emp_count” variable whose salary is more than 40,000”:

The output verified that only filtered data is assigned to the given variable.

PostgreSQL supports a “ PL/pgSQL SELECT INTO” statement that assists us in storing the table's data into a specific variable. The stated command fetches the data from a particular table and assigns it to a specific variable. Different clauses can be used with the PL/pgSQL SELECT INTO statement for different purposes, such as the WHERE clause, GROUP BY clause, JOIN, etc. This post presented a detailed guide on how to assign a table's data to a variable using the SELECT INTO command in Postgres.

Get Postgres Help!

Recent blogs

Postgresql length() function with practical examples.

May 9, 2024, 5:57 a.m.

How to Show Databases in PostgreSQL

May 6, 2024, 5:44 p.m.

How to Create a Database in PostgreSQL

May 2, 2024, 2:29 p.m.

How to Install PostgreSQL (psql) on Arch Linux

May 2, 2024, 6:02 a.m.

How to Use LIMIT Clause in PostgreSQL

May 1, 2024, 7:12 p.m.

How to Install PostgreSQL on Ubuntu 24.04

May 1, 2024, 8:49 a.m.

How to Use RENAME TABLE Statement in PostgreSQL

April 29, 2024, 5:10 p.m.

PostgreSQL TIME Data Type With Examples

April 29, 2024, 5:07 a.m.

Home » PostgreSQL PL/pgSQL » PL/pgSQL CASE Statement

PL/pgSQL CASE Statement

Summary : in this tutorial, you will learn about the PL/pgSQL case that executes statements based on a certain condition.

Introduction to PL/pgSQL CASE Statment

Besides the if statement , PostgreSQL provides the case statements that allow you to execute a block of code based on conditions.

The case statement selects a when section to execute from a list of when sections based on a condition.

The case statement has two forms:

• Simple case statement
• Searched case statement

Notice that you should not be confused about the case statement and case expression . The case expression evaluates to a value while the case statement selects a section to execute based on conditions.

1) Simple case statement

Here’s the basic syntax of the simple case statement:

The search-expression is an expression that evaluates to a result.

The case statement compares the result of the search-expression with the expression in each when branch using equal operator ( = ) from top to bottom.

If the case statement finds a match, it will execute the corresponding when section. Additionally, it stops checking the remaining when sections

If the case statement cannot find any match, it will execute the else section.

The else section is optional. If the result of the search-expression does not match expression in the when sections and the else section does not exist, the case statement will raise a  case_not_found exception.

The following example shows how to use a simple case statement:

How it works.

First, select the rental rate of the film with id 100.

Second, assign price segment to the price_segment variable if the film id 100 exists or a message otherwise.

Based on the rental rates 0.99, 2.99, or 4.99, the case statement assigns mass, mainstream, or high-end to the price_segment variable. If the rental rate is not one of these values, the case statement assigns the string Unspecified to the price_segment variable.

The following flowchart illustrates the simple case statement in this example:

2) Searched case statement

The following syntax shows the basic syntax of the searched case statement:

In this syntax, the case statement evaluates the boolean expressions sequentially from top to bottom until it finds an expression that evaluates to true

Subsequently, the case statement executes the corresponding when section and immediately stops searching for the remaining expressions.

If no expression evaluates to true, the case statement will execute the else section.

The else section is optional. If you omit the else section and there is no expression evaluated to true , the case statement will raise the  case_not_found exception.

The following example illustrates how to use a simple case statement:

How it works:

• First, select the total payment paid by the customer id 100 from the payment table.
• Then, assign the service level to the customer based on the total payment

The following diagram illustrates the logic:

Notice that the searched case statement is similar to the if then elsif statement .

• Use the case statement to execute a section based on certain conditions.
• Use a simple case statement to compare a value with a list of values and if a match is found, execute a section.
• Use a searched case statement to evaluate a list of conditions and execute a section if the condition is true.

41.13. Porting from Oracle PL/SQL #

This section explains differences between PostgreSQL 's PL/pgSQL language and Oracle's PL/SQL language, to help developers who port applications from Oracle ® to PostgreSQL .

PL/pgSQL is similar to PL/SQL in many aspects. It is a block-structured, imperative language, and all variables have to be declared. Assignments, loops, and conditionals are similar. The main differences you should keep in mind when porting from PL/SQL to PL/pgSQL are:

If a name used in an SQL command could be either a column name of a table used in the command or a reference to a variable of the function, PL/SQL treats it as a column name. By default, PL/pgSQL will throw an error complaining that the name is ambiguous. You can specify plpgsql.variable_conflict = use_column to change this behavior to match PL/SQL , as explained in Section 41.11.1 . It's often best to avoid such ambiguities in the first place, but if you have to port a large amount of code that depends on this behavior, setting variable_conflict may be the best solution.

In PostgreSQL the function body must be written as a string literal. Therefore you need to use dollar quoting or escape single quotes in the function body. (See Section 41.12.1 .)

Data type names often need translation. For example, in Oracle string values are commonly declared as being of type varchar2 , which is a non-SQL-standard type. In PostgreSQL , use type varchar or text instead. Similarly, replace type number with numeric , or use some other numeric data type if there's a more appropriate one.

Instead of packages, use schemas to organize your functions into groups.

Since there are no packages, there are no package-level variables either. This is somewhat annoying. You can keep per-session state in temporary tables instead.

Integer FOR loops with REVERSE work differently: PL/SQL counts down from the second number to the first, while PL/pgSQL counts down from the first number to the second, requiring the loop bounds to be swapped when porting. This incompatibility is unfortunate but is unlikely to be changed. (See Section 41.6.5.5 .)

FOR loops over queries (other than cursors) also work differently: the target variable(s) must have been declared, whereas PL/SQL always declares them implicitly. An advantage of this is that the variable values are still accessible after the loop exits.

There are various notational differences for the use of cursor variables.

41.13.1. Porting Examples #

Example 41.9 shows how to port a simple function from PL/SQL to PL/pgSQL .

Example 41.9. Porting a Simple Function from PL/SQL to PL/pgSQL

Here is an Oracle PL/SQL function:

Let's go through this function and see the differences compared to PL/pgSQL :

The type name varchar2 has to be changed to varchar or text . In the examples in this section, we'll use varchar , but text is often a better choice if you do not need specific string length limits.

The RETURN key word in the function prototype (not the function body) becomes RETURNS in PostgreSQL . Also, IS becomes AS , and you need to add a LANGUAGE clause because PL/pgSQL is not the only possible function language.

In PostgreSQL , the function body is considered to be a string literal, so you need to use quote marks or dollar quotes around it. This substitutes for the terminating / in the Oracle approach.

The show errors command does not exist in PostgreSQL , and is not needed since errors are reported automatically.

This is how this function would look when ported to PostgreSQL :

Example 41.10 shows how to port a function that creates another function and how to handle the ensuing quoting problems.

Example 41.10. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL

The following procedure grabs rows from a SELECT statement and builds a large function with the results in IF statements, for the sake of efficiency.

This is the Oracle version:

Here is how this function would end up in PostgreSQL :

Notice how the body of the function is built separately and passed through quote_literal to double any quote marks in it. This technique is needed because we cannot safely use dollar quoting for defining the new function: we do not know for sure what strings will be interpolated from the referrer_key.key_string field. (We are assuming here that referrer_key.kind can be trusted to always be host , domain , or url , but referrer_key.key_string might be anything, in particular it might contain dollar signs.) This function is actually an improvement on the Oracle original, because it will not generate broken code when referrer_key.key_string or referrer_key.referrer_type contain quote marks.

Example 41.11 shows how to port a function with OUT parameters and string manipulation. PostgreSQL does not have a built-in instr function, but you can create one using a combination of other functions. In Section 41.13.3 there is a PL/pgSQL implementation of instr that you can use to make your porting easier.

Example 41.11. Porting a Procedure With String Manipulation and OUT Parameters from PL/SQL to PL/pgSQL

The following Oracle PL/SQL procedure is used to parse a URL and return several elements (host, path, and query).

Here is a possible translation into PL/pgSQL :

This function could be used like this:

Example 41.12 shows how to port a procedure that uses numerous features that are specific to Oracle.

Example 41.12. Porting a Procedure from PL/SQL to PL/pgSQL

The Oracle version:

This is how we could port this procedure to PL/pgSQL :

41.13.2. Other Things to Watch For #

This section explains a few other things to watch for when porting Oracle PL/SQL functions to PostgreSQL .

41.13.2.1. Implicit Rollback after Exceptions #

In PL/pgSQL , when an exception is caught by an EXCEPTION clause, all database changes since the block's BEGIN are automatically rolled back. That is, the behavior is equivalent to what you'd get in Oracle with:

If you are translating an Oracle procedure that uses SAVEPOINT and ROLLBACK TO in this style, your task is easy: just omit the SAVEPOINT and ROLLBACK TO . If you have a procedure that uses SAVEPOINT and ROLLBACK TO in a different way then some actual thought will be required.

41.13.2.2.  EXECUTE #

The PL/pgSQL version of EXECUTE works similarly to the PL/SQL version, but you have to remember to use quote_literal and quote_ident as described in Section 41.5.4 . Constructs of the type EXECUTE 'SELECT * FROM $1'; will not work reliably unless you use these functions.

41.13.2.3. Optimizing PL/pgSQL Functions #

PostgreSQL gives you two function creation modifiers to optimize execution: “ volatility ” (whether the function always returns the same result when given the same arguments) and “ strictness ” (whether the function returns null if any argument is null). Consult the CREATE FUNCTION reference page for details.

When making use of these optimization attributes, your CREATE FUNCTION statement might look something like this:

41.13.3. Appendix #

This section contains the code for a set of Oracle-compatible instr functions that you can use to simplify your porting efforts.