Dynamic SQL In PostgreSQL || Use Cases And Usage Of Dynamic SQL With Exa...

Dynamic SQL in PostgreSQL | Use Cases and Examples Explained in pgAdmin

In this video, we'll dive into the world of dynamic SQL in PostgreSQL using pgAdmin, exploring various use cases and providing detailed examples to enhance your understanding. Dynamic SQL allows you to construct and execute SQL statements at runtime, offering flexibility and power in database management and manipulation. Here's what we'll cover:

1. Run-Time DDL Example

   • Creating a sample table and a stored procedure to add columns dynamically.
   • Example: Adding a new column email to the employees table.

2. Run-Time SCL (Session Control)

   • Setting session variables and roles dynamically.
   • Examples: Setting the session timezone and changing roles within a session.

3. Dynamic Columns with Conditions

   • Filtering data dynamically based on various conditions.
   • Example: Filtering employees by department using a dynamic WHERE clause.

4. Dynamic SELECT INTO Queries

   • Using dynamic SQL to fetch specific column values.
   • Examples: Fetching salaries based on employee names and dynamically constructing queries with multiple column retrieval.

5. OPEN Refcursor RETURN Query

   • Executing dynamic queries and returning results using refcursors.
   • Example: Returning all employee details using a dynamic query.

6. Dynamic Multiple-Row Query with Open Loop

   • Creating and executing queries that return multiple rows using loops.
   • Example: Fetching all employees from a specific department.

7. Dynamic DML (Data Manipulation Language)

   • Updating table data dynamically.
   • Example: Updating the salary of an employee based on their ID.

By the end of this video, you'll have a solid grasp of how to leverage dynamic SQL in PostgreSQL to perform a wide range of database operations efficiently. Whether you're adding new columns on the fly, setting session parameters, or filtering and updating data dynamically, these examples will provide you with the tools and knowledge to implement dynamic SQL in your own projects.

PostgreSQL, dynamic SQL, dynamic SQL examples, pgAdmin, dynamic SQL usage, PostgreSQL tutorial, advanced SQL, SQL scripting, database management, SQL use cases, dynamic queries

Don't forget to like, share, and subscribe for more in-depth tutorials on PostgreSQL and other database technologies!

Run Time DDL Example

-------------------------------

-- Create a sample table

CREATE TABLE employees (

    id SERIAL PRIMARY KEY,

    name VARCHAR(100),

    department VARCHAR(100),

    salary NUMERIC(10, 2)

);

-- Sample stored procedure for dynamic DDL command

CREATE OR REPLACE FUNCTION alter_table(column_name VARCHAR, data_type VARCHAR) RETURNS VOID AS $$

BEGIN

    EXECUTE format('ALTER TABLE employees ADD COLUMN %I %s', column_name, data_type);

END;

$$ LANGUAGE plpgsql;

-- Call the stored procedure to add a new column dynamically

SELECT alter_table('email', 'VARCHAR(255)');

Run Time SCL, Session Control

------------------------------

-- Create a custom function to set session variables

CREATE OR REPLACE FUNCTION set_session_variable(var_name TEXT, var_value TEXT) RETURNS VOID AS $$

BEGIN

    EXECUTE format('SET SESSION %s TO %L', var_name, var_value);

END;

$$ LANGUAGE plpgsql;

-- Create a custom function to set session role

CREATE OR REPLACE FUNCTION set_session_role(role_name TEXT) RETURNS VOID AS $$

BEGIN

    EXECUTE format('SET ROLE %s', role_name);

END;

$$ LANGUAGE plpgsql;

select now();

-- Set a session variable

SELECT set_session_variable('timezone', 'UTC');

select now();

-- Set session role

SELECT set_session_role('dvdrental');

SELECT set_session_role('postgres');

Dynamic Columns at run time + Where Condition

------------------------------------------------

Drop table employees;

-- Create a sample table

CREATE TABLE employees (

    id SERIAL PRIMARY KEY,

    name VARCHAR(100),

    department VARCHAR(100),

    salary NUMERIC(10, 2)

);

-- Insert some sample data

INSERT INTO employees (name, department, salary) VALUES 

('John Doe', 'HR', 50000.00),

('Jane Smith', 'IT', 60000.00),

('Alice Johnson', 'Finance', 55000.00);

-- Create a stored procedure for dynamic WHERE condition

CREATE OR REPLACE FUNCTION filter_employees(attr_name TEXT, attr_value TEXT) RETURNS SETOF employees AS $$

BEGIN

    RETURN QUERY EXECUTE format('SELECT * FROM employees WHERE %I = %L', attr_name, attr_value);

END;

$$ LANGUAGE plpgsql;

-- Filter employees dynamically by department

SELECT * FROM filter_employees('department', 'IT');

Select INTO query

--------------------

-- Create a stored procedure for dynamic WHERE condition

CREATE OR REPLACE FUNCTION filter_employees2(attr_name TEXT, attr_value TEXT) RETURNS numeric AS $$

DECLARE

lv_salary numeric(10,0);

BEGIN

EXECUTE format('SELECT salary FROM employees WHERE %I = %L', attr_name, attr_value) INTO lv_salary;

return lv_salary;

END;

$$ LANGUAGE plpgsql;

-- Filter employees dynamically by department

SELECT * FROM filter_employees2('name', 'John Doe');

Select INTO Using query

-------------------------

-- Create a stored procedure for dynamic WHERE condition

CREATE OR REPLACE FUNCTION filter_employees3(attr_name TEXT, attr_value TEXT) RETURNS char AS $$

DECLARE

lv_salary numeric(10,0);

lv_name char(30);

BEGIN

EXECUTE 'SELECT salary, name FROM employees WHERE '||$1||' = '||$2||''

USING attr_name, attr_value INTO lv_salary,lv_name;

   

   return lv_name||' Having Salary: '||lv_salary;

END;

$$ LANGUAGE plpgsql;

-- Filter employees dynamically by department

SELECT * FROM filter_employees3('id', '1');

OPEN Refcursor RETURN Query

--------------------------

CREATE OR REPLACE FUNCTION get_employee_details(output_refcursor refcursor) RETURNS refcursor AS $$

DECLARE

    dynamic_query TEXT;

BEGIN

    dynamic_query := 'SELECT * FROM employees'; -- Your dynamic query here

    OPEN output_refcursor FOR EXECUTE dynamic_query;

    RETURN output_refcursor;

END;

$$ LANGUAGE plpgsql;

select get_employee_details('output_refcursor');

fetch all in output_refcursor;

Dynamic Multiple-Row Query, Open loop

-------------------------------------

Drop table employees;

-- Create a sample table

CREATE TABLE employees (

    id SERIAL PRIMARY KEY,

    name VARCHAR(100),

    department VARCHAR(100)

);

-- Insert some sample data

INSERT INTO employees (name, department) VALUES 

('John Doe', 'HR'),

('Jane Smith', 'IT'),

('Alice Johnson', 'Finance');

-- Create a function to dynamically execute query and return multiple rows

CREATE OR REPLACE FUNCTION dynamic_query(condition TEXT) RETURNS TABLE (id INT, name TEXT, department TEXT) AS $$

DECLARE

    emp_record employees%ROWTYPE;

    query TEXT;

BEGIN

    query := 'SELECT * FROM employees WHERE ' || condition;

    FOR emp_record IN EXECUTE query LOOP

        id := emp_record.id;

        name := emp_record.name;

        department := emp_record.department;

        RETURN NEXT;

    END LOOP;

    RETURN;

END;

$$ LANGUAGE plpgsql;

SELECT * FROM dynamic_query('department = ''IT''');

DML

----

Drop table employees;

-- Create a sample table

CREATE TABLE employees (

    id SERIAL PRIMARY KEY,

    name VARCHAR(100),

    department VARCHAR(100),

    salary NUMERIC(10, 2)

);

-- Insert some sample data

INSERT INTO employees (name, department, salary) VALUES 

('John Doe', 'HR', 50000.00),

('Jane Smith', 'IT', 60000.00),

('Alice Johnson', 'Finance', 55000.00);

CREATE OR REPLACE FUNCTION update_column_value(table_name TEXT, column_name TEXT, column_value TEXT, condition_column TEXT, condition_value TEXT) RETURNS VOID AS $$

DECLARE

    sql_statement TEXT;

BEGIN

    sql_statement := format('UPDATE %I SET %I = %L WHERE %I = %L', table_name, column_name, column_value, condition_column, condition_value);

    EXECUTE sql_statement;

END;

$$ LANGUAGE plpgsql;

SELECT update_column_value('employees', 'salary', '58700', 'id', '1');

select * from employees;