PL/pgSQL – Variables

ByAdmin

In PostgreSQL’s PL/pgSQL language, you can declare and use variables for storing and manipulating data within stored procedures, functions, and triggers. PL/pgSQL supports various data types for variables, including the standard SQL data types and custom types defined in your database.

Here’s how you can declare and use variables in PL/pgSQL:

Declaration:

You can declare a variable using the DECLARE statement within the body of your PL/pgSQL code block. You should specify the variable name, data type, and optionally an initial value.

DECLARE variable_name data_type [ := initial_value ];

Initialization:

You can initialize a variable when you declare it, or you can set its value later using the := operator.

DECLARE my_var INT := 42; my_var := 100;

Usage:

You can use variables in your PL/pgSQL code like any other SQL expression.

DECLARE total_sales DECIMAL(10, 2) := 0; 

SELECT SUM(sale_amount) INTO total_sales FROM sales;

Here’s a simple PL/pgSQL function that demonstrates variable usage:

CREATE OR REPLACE FUNCTION calculate_total(a INT, b INT) RETURNS INT 
AS $$ 
DECLARE 
  result INT; 
BEGIN 
  result := a + b; 
  RETURN result; 
END; 
$$ LANGUAGE plpgsql;

In this example:

  • We declare a variable result of type INT.
  • We assign the sum of a and b to the result variable.
  • We return the value of the result variable.

You can call this function like any other SQL function, passing values for a and b, and it will return the sum of the two values.

Similar Posts

PL/pgSQL – Constants

ByAdmin

In PostgreSQL’s PL/pgSQL language, you can create constants using the CONSTANT keyword. Constants are values that don’t change during the execution of a PL/pgSQL block and are typically used for storing values that you want to use without modification throughout your code. Constants can be of various data types, including numeric, text, or custom types….

PL/pgSQL – IF Statement

ByAdmin

In PostgreSQL’s PL/pgSQL, you can use the IF statement to conditionally execute a block of code based on a specified condition. The IF statement allows you to branch your code flow, executing different code blocks depending on whether the condition is true or false. Here’s the basic syntax of the IF statement: IF condition THEN…

PL/pgSQL – Functions

ByAdmin

PL/pgSQL functions are named blocks of code written in the PL/pgSQL procedural language in PostgreSQL database. These functions can accept parameters, perform calculations, execute database queries, and return values. PL/pgSQL is a powerful and flexible language for writing complex database logic and stored procedures. Here’s the basic structure of a PL/pgSQL function: CREATE OR REPLACE…

PL/pgSQL – LOOP Statement

ByAdmin

In PostgreSQL’s PL/pgSQL, you can use loop statements to create loops and iterate over a sequence of statements. PL/pgSQL provides several types of loop statements, including LOOP, WHILE, and FOR, each with its own use cases. Here, we’ll focus on the LOOP statement, which creates an unconditional loop that continues until explicitly terminated using the…

PL/pgSQL – Cursors

ByAdmin

PL/pgSQL supports the use of cursors to retrieve and manipulate result sets from SQL queries in PostgreSQL database. Cursors are particularly useful when you need to work with large result sets or when you want to process rows one by one within a PL/pgSQL function or procedure. Here’s a basic overview of cursors in PL/pgSQL:…

Programming in PostgreSQL Database

ByAdmin

In PostgreSQL, you can write stored procedures, functions, and triggers using various programming languages. PostgreSQL supports multiple procedural languages, including: When you create a function or procedure in PostgreSQL, you can specify the language you want to use, and then write the code accordingly. Here’s a basic example of creating a function using PL/pgSQL: sqlCopy…