PostgreSQL Tutorial

Data Types

Querying & Filtering Data

Managing Tables

Modifying Data

Conditionals

Control Flow

Transactions & Constraints

Working with JOINS & Schemas

Roles & Permissions

Working with Sets

Subquery & CTEs

User-defined Functions

Important In-Built Functions

PostgreSQL PL/pgSQL

Variables & Constants

Stored Procedures

Working with Triggers

Working with Views & Indexes

Errors & Exception Handling

PostgreSQL - Variables

In the context of PostgreSQL, variables aren't used in the same way as in many other programming languages or even in some other database management systems. However, there are mechanisms to achieve similar outcomes. Here's an overview:

1. PL/pgSQL Variables:

If you're writing functions or procedures in the PL/pgSQL procedural language, you can use variables. In this context, they behave as you might expect:

DO $$
DECLARE
   my_variable INTEGER;
BEGIN
   my_variable := 10;
   RAISE NOTICE 'Value of my_variable: %', my_variable;
END $$;

In the above block:

We declare a variable named my_variable of type INTEGER.
We then assign it a value of 10.
Finally, we use the RAISE NOTICE command to print its value.

2. SQL Functions:

For SQL functions, you can use arguments as a kind of variable:

CREATE OR REPLACE FUNCTION greet(name TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN 'Hello, ' || name;
END;
$$ LANGUAGE plpgsql;

3. psql Variables:

If you're using the psql command-line client, you can use the \set command to define a variable:

\set my_variable 'Hello, world!'

And then use it in SQL statements:

SELECT :'my_variable';

4. Temporary Tables or Session-Level Tables:

For complex scenarios where you need to keep some session-level data, you might consider using temporary tables:

CREATE TEMP TABLE my_temp_table AS
SELECT ...

This table will last for your session and will be automatically dropped at the end.

5. Configuration Parameters:

You can also use the SET command to change a configuration parameter's value, effectively using it as a kind of variable:

SET search_path TO myschema, public;

However, these aren't variables in the traditional sense; they're session-level configuration settings.

6. Extensions and External Tools:

Extensions like pgTAP for unit testing or tools like pgAdmin might provide more advanced variable-like functionality, but that's external to core PostgreSQL.

Important Note:

Always be cautious when passing user-generated data into SQL to prevent SQL injection attacks. For PL/pgSQL, the use of variables inherently helps with this, as they don't directly interpolate their values into the SQL strings. For psql and other scenarios, always validate and sanitize inputs.

In summary, PostgreSQL doesn't have session-level SQL variables in the same sense as some other DBMSs, but depending on the context, there are various methods to achieve similar functionality.

PostgreSQL variable declaration example: Declare and use a variable in a PL/pgSQL block:

DO $$ 
DECLARE 
    my_var INT := 42;
BEGIN 
    RAISE NOTICE 'The value is %', my_var;
END $$;

How to use variables in PostgreSQL: Use variables in a simple SQL statement:

DO $$ 
DECLARE 
    user_id INT := 1001;
BEGIN 
    INSERT INTO user_table (id, name) VALUES (user_id, 'John Doe');
END $$;

PostgreSQL set variable in SELECT statement: Set a variable within a SELECT statement:

DO $$ 
DECLARE 
    total_sales DECIMAL;
BEGIN 
    SELECT SUM(sales_amount) INTO total_sales FROM sales;
    RAISE NOTICE 'Total Sales: %', total_sales;
END $$;

Scope of variables in PostgreSQL: Understand the scope of variables in a block:

DO $$ 
DECLARE 
    outer_var INT := 10;
BEGIN 
    DECLARE 
        inner_var INT := 5;
    BEGIN 
        RAISE NOTICE 'Outer Variable: %, Inner Variable: %', outer_var, inner_var;
    END;
END $$;

Global variables in PostgreSQL: Use global variables across functions:

CREATE OR REPLACE FUNCTION set_global_var() RETURNS VOID AS $$
DECLARE
    global_var INT := 100;
BEGIN
    -- Perform operations using global_var
END;
$$ LANGUAGE plpgsql;

Using variables in stored procedures in PostgreSQL: Declare and use variables in a stored procedure:

CREATE OR REPLACE PROCEDURE calculate_average()
LANGUAGE plpgsql AS $$
DECLARE
    total_sum INT := 0;
    total_count INT := 0;
    average FLOAT;
BEGIN
    -- Perform calculations and update variables
END;
$$;

Dynamic SQL with variables in PostgreSQL: Use variables in dynamic SQL statements:

DO $$ 
DECLARE 
    table_name VARCHAR := 'my_table';
    query_string VARCHAR;
BEGIN 
    query_string := 'SELECT * FROM ' || table_name;
    EXECUTE query_string;
END $$;

PostgreSQL environment variables: Access environment variables in PostgreSQL:

DO $$ 
DECLARE 
    env_var_value VARCHAR;
BEGIN 
    env_var_value := current_setting('my_environment_variable');
    RAISE NOTICE 'Environment Variable Value: %', env_var_value;
END $$;

PostgreSQL session variables: Use session variables for temporary storage:

DO $$ 
DECLARE 
    session_var INT;
BEGIN 
    -- Set session variable
    SET session_var = 42;

    -- Use session variable in queries
    SELECT * FROM my_table WHERE column_value = session_var;
END $$;