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:
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.
Run-Time SCL (Session Control)
- Setting session variables and roles dynamically.
- Examples: Setting the session timezone and changing roles within a session.
Dynamic Columns with Conditions
- Filtering data dynamically based on various conditions.
- Example: Filtering employees by department using a dynamic WHERE clause.
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.
OPEN Refcursor RETURN Query
- Executing dynamic queries and returning results using refcursors.
- Example: Returning all employee details using a dynamic query.
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.
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;
