Dynamic Table and Column Selection in PostgreSQL

Introduction

In most SQL queries, you know exactly which table and columns you want to work with. But what if you don’t know the table or columns beforehand?

In many real-world scenarios, such as multi-tenant systems, reporting dashboards, or data exploration tools, you may need to choose tables and columns dynamically at runtime.

PostgreSQL supports this through dynamic SQL, which allows you to build and execute queries on the fly.

Pioneers in Cloud Consulting & Migration Services

Reduced infrastructural costs
Accelerated application deployment

Get Started

Importance of Dynamic Table and Column Selection

Dynamic queries are helpful when:

Multi-Tenant Applications – Each tenant might have its own table, such as sales_tenant1, sales_tenant2, etc., and you want a single function to query any of them.
Customizable Reports – Users may select which columns they want in the output from a given dataset.
Data Exploration Tools – Analytical dashboards often let users pick table and column combinations for ad-hoc analysis.
ETL (Extract, Transform, Load) Pipelines – You might need to copy data between tables whose names are generated dynamically based on dates or configurations.

Challenges of Dynamic SQL

Dynamic table and column selection isn’t as straightforward as passing variable values into a query, because table and column names cannot be parameterized in standard SQL.

Implementing Dynamic Selection in PostgreSQL

Dynamic SQL is most commonly implemented inside a PL/pgSQL function using the EXECUTE statement in PostgreSQL.

Example 1: Selecting from a Dynamic Table

CREATE OR REPLACE FUNCTION get_all_from_table(tbl_name TEXT)
RETURNS SETOF RECORD
LANGUAGE plpgsql AS $$
DECLARE
    sql TEXT;
BEGIN
    sql := 'SELECT * FROM ' || quote_ident(tbl_name);
    RETURN QUERY EXECUTE sql;
END;
$$;

CREATE OR REPLACE FUNCTION get_all_from_table(tbl_name TEXT)

RETURNS SETOF RECORD

LANGUAGE plpgsql AS $$

DECLARE

sql TEXT;

BEGIN

sql := 'SELECT * FROM ' || quote_ident(tbl_name);

RETURN QUERY EXECUTE sql;

END;

$$;

How it works:

quote_ident() ensures the table name is properly quoted to avoid SQL injection and syntax errors.

EXECUTE runs the SQL string we built.

SELECT * FROM get_all_from_table('customers') AS t(name TEXT, email TEXT);

1	SELECT * FROM get_all_from_table('customers') AS t(name TEXT, email TEXT);

Example 2: Selecting Specific Columns Dynamically

What if the user wants only specific columns? Both the table name and the list of columns can be passed:

CREATE OR REPLACE FUNCTION get_columns_from_table(
    tbl_name TEXT,
    cols TEXT
)
RETURNS SETOF RECORD
LANGUAGE plpgsql AS $$
DECLARE
    sql TEXT;
BEGIN
    sql := 'SELECT ' || cols || ' FROM ' || quote_ident(tbl_name);
    RETURN QUERY EXECUTE sql;
END;
$$;

CREATE OR REPLACE FUNCTION get_columns_from_table(

tbl_name TEXT,

cols TEXT

)

RETURNS SETOF RECORD

LANGUAGE plpgsql AS $$

DECLARE

sql TEXT;

BEGIN

sql := 'SELECT ' || cols || ' FROM ' || quote_ident(tbl_name);

RETURN QUERY EXECUTE sql;

END;

$$;

Usage:

SELECT * FROM get_columns_from_table('customers', 'name, email')
AS t(name TEXT, email TEXT);

1 2	SELECT * FROM get_columns_from_table('customers', 'name, email') AS t(name TEXT, email TEXT);

Note: Column names are concatenated directly into the query string, so you must validate them to prevent SQL injection.

Example 3: Dynamic WHERE Clauses

We can extend this idea to include filters:

CREATE OR REPLACE FUNCTION get_filtered_data(
    tbl_name TEXT,
    cols TEXT,
    filter TEXT
)
RETURNS SETOF RECORD
LANGUAGE plpgsql AS $$
DECLARE
    sql TEXT;
BEGIN
    sql := 'SELECT ' || cols || ' FROM ' || quote_ident(tbl_name) ||
           ' WHERE ' || filter;
    RETURN QUERY EXECUTE sql;
END;
$$;

CREATE OR REPLACE FUNCTION get_filtered_data(

tbl_name TEXT,

cols TEXT,

filter TEXT

)

RETURNS SETOF RECORD

LANGUAGE plpgsql AS $$

DECLARE

sql TEXT;

BEGIN

sql := 'SELECT ' || cols || ' FROM ' || quote_ident(tbl_name) ||

' WHERE ' || filter;

RETURN QUERY EXECUTE sql;

END;

$$;

Usage:

SELECT * FROM get_filtered_data(
    'customers',
    'name, email',
    'name LIKE ''A%'''
) AS t(name TEXT, email TEXT);

SELECT * FROM get_filtered_data(

'customers',

'name, email',

'name LIKE ''A%'''

) AS t(name TEXT, email TEXT);

Preventing SQL Injection in Dynamic Queries

If user input is not cleaned up, dynamic SQL can be hazardous. An attacker could use malicious SQL injection to remove tables or reveal private information.

To mitigate this:

Use quote_ident() for table and column names.

Use quote_literal() for values.

Validate inputs against whitelists of allowed tables and columns.

Avoid concatenating untrusted strings directly into SQL.

Example with Safe Handling:

CREATE OR REPLACE FUNCTION safe_get_data(
    tbl_name TEXT,
    col_name TEXT,
    value TEXT
)
RETURNS SETOF RECORD
LANGUAGE plpgsql AS $$
DECLARE
    sql TEXT;
BEGIN
    sql := 'SELECT * FROM ' || quote_ident(tbl_name) ||
           ' WHERE ' || quote_ident(col_name) ||
           ' = ' || quote_literal(value);
    RETURN QUERY EXECUTE sql;
END;
$$;

CREATE OR REPLACE FUNCTION safe_get_data(

tbl_name TEXT,

col_name TEXT,

value TEXT

)

RETURNS SETOF RECORD

LANGUAGE plpgsql AS $$

DECLARE

sql TEXT;

BEGIN

sql := 'SELECT * FROM ' || quote_ident(tbl_name) ||

' WHERE ' || quote_ident(col_name) ||

' = ' || quote_literal(value);

RETURN QUERY EXECUTE sql;

END;

$$;

Real-World Example: Multi-Tenant Reporting

Assume that each customer has its own sales table, such as sales_client1, sales_client2, etc. We can build a single reporting function:

CREATE OR REPLACE FUNCTION get_sales_for_client(
    client_id INT,
    start_date DATE,
    end_date DATE
)
RETURNS SETOF RECORD
LANGUAGE plpgsql AS $$
DECLARE
    tbl_name TEXT;
    sql TEXT;
BEGIN
    tbl_name := 'sales_client' || client_id;
    sql := 'SELECT order_id, amount, order_date FROM ' || quote_ident(tbl_name) ||
           ' WHERE order_date BETWEEN ' || quote_literal(start_date) ||
           ' AND ' || quote_literal(end_date);
    RETURN QUERY EXECUTE sql;
END;
$$;

CREATE OR REPLACE FUNCTION get_sales_for_client(

client_id INT,

start_date DATE,

end_date DATE

)

RETURNS SETOF RECORD

LANGUAGE plpgsql AS $$

DECLARE

tbl_name TEXT;

sql TEXT;

BEGIN

tbl_name := 'sales_client' || client_id;

sql := 'SELECT order_id, amount, order_date FROM ' || quote_ident(tbl_name) ||

' WHERE order_date BETWEEN ' || quote_literal(start_date) ||

' AND ' || quote_literal(end_date);

RETURN QUERY EXECUTE sql;

END;

$$;

Usage:

SELECT * FROM get_sales_for_client(2, '2025-01-01', '2025-01-31')
AS t(order_id INT, amount NUMERIC, order_date DATE);

1 2	SELECT * FROM get_sales_for_client(2, '2025-01-01', '2025-01-31') AS t(order_id INT, amount NUMERIC, order_date DATE);

Best Practices for Dynamic Table and Column Selection

Use whitelists for table and column names and always verify input.
Use quote_literal() and quote_ident() instead of concatenating raw strings..
Return defined column structures — Improves clarity and avoids mismatches.
Monitor performance — Cache results where possible.
Limit privileges — Restrict the database user running dynamic SQL to reduce damage in case of injection.

Conclusion

One of PostgreSQL’s most useful features for creating versatile applications is dynamic table and column selection. Whether you’re implementing multi-tenant architectures, building customizable dashboards, or processing data across changing schemas, dynamic SQL can make your queries smarter.

By using safe quoting functions, validating inputs, and applying best practices, you can harness dynamic queries without compromising your database integrity.

When used wisely, dynamic SQL in PostgreSQL can turn a static, rigid data access layer into a dynamic, user-driven engine for data exploration and analysis.

Drop a query if you have any questions regarding Dynamic Tables and we will get back to you quickly.

Empowering organizations to become ‘data driven’ enterprises with our Cloud experts.

Reduced infrastructure costs
Timely data-driven decisions

Get Started

About CloudThat

CloudThat is an award-winning company and the first in India to offer cloud training and consulting services worldwide. As a Microsoft Solutions Partner, AWS Advanced Tier Training Partner, and Google Cloud Platform Partner, CloudThat has empowered over 850,000 professionals through 600+ cloud certifications winning global recognition for its training excellence including 20 MCT Trainers in Microsoft’s Global Top 100 and an impressive 12 awards in the last 8 years. CloudThat specializes in Cloud Migration, Data Platforms, DevOps, IoT, and cutting-edge technologies like Gen AI & AI/ML. It has delivered over 500 consulting projects for 250+ organizations in 30+ countries as it continues to empower professionals and enterprises to thrive in the digital-first world.

FAQs

1. What does “dynamic table and column selection” mean in PostgreSQL?

ANS: – It means building and executing SQL queries where the table name, column names, or both are determined at runtime instead of being hardcoded in the query.

2. How can I dynamically select columns in PostgreSQL?

ANS: – You can pass a string of column names to your function and build the SQL query with EXECUTE, but you must validate and quote them to prevent SQL injection.

3. Does dynamic SQL affect performance in PostgreSQL?

ANS: – Yes, slightly. Since queries are generated at runtime, they don’t benefit from PostgreSQL’s prepared statement caching, which can make them slower if executed repeatedly.

WRITTEN BY Hitesh Verma

Hitesh works as a Senior Research Associate – Data & AI/ML at CloudThat, focusing on developing scalable machine learning solutions and AI-driven analytics. He works on end-to-end ML systems, from data engineering to model deployment, using cloud-native tools. Hitesh is passionate about applying advanced AI research to solve real-world business problems.