DEV Community

Cover image for Using DBMS_SQL for Dynamic SQL Execution in EDB Postgres Advanced Server 16
Shiv Iyer
Shiv Iyer

Posted on

Using DBMS_SQL for Dynamic SQL Execution in EDB Postgres Advanced Server 16

A common use case for using DBMS_SQL subprograms in EDB Postgres Advanced Server 16 is dynamic SQL execution in stored procedures. This is particularly useful when the SQL statement to be executed is not known at compile time and must be constructed at runtime.

Using DBMS_SQL subprograms in EDB Postgres Advanced Server 16 allows for dynamic SQL execution. Here's a practical example:

Suppose you want to create a function that dynamically selects data from a given table and column names provided at runtime.

  1. Function Definition:

    CREATE OR REPLACE FUNCTION dynamic_query(table_name text, column_name text)
    RETURNS SETOF record AS $$
    DECLARE
        cur_id INTEGER;
        query TEXT;
        result_set RECORD;
    BEGIN
        cur_id := DBMS_SQL.OPEN_CURSOR();
        query := 'SELECT ' || quote_ident(column_name) || ' FROM ' || quote_ident(table_name);
        DBMS_SQL.PARSE(cur_id, query);
        DBMS_SQL.DEFINE_COLUMN(cur_id, 1, result_set);
        DBMS_SQL.EXECUTE(cur_id);
    
        LOOP
            EXIT WHEN NOT DBMS_SQL.FETCH_ROWS(cur_id) > 0;
            DBMS_SQL.COLUMN_VALUE(cur_id, 1, result_set);
            RETURN NEXT result_set;
        END LOOP;
    
        DBMS_SQL.CLOSE_CURSOR(cur_id);
    END;
    $$ LANGUAGE plpgsql;
    
    
  2. Function Usage:

    -- Assuming you have a table 'employees' with a column 'name'
    SELECT * FROM dynamic_query('employees', 'name');
    
    

In this example, the dynamic_query function takes a table name and column name as input, constructs a SELECT query, executes it, and returns the result set. This approach is useful when you need to build and execute SQL queries dynamically based on runtime conditions.

More PostgreSQL Blogs

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay