Postgres Dynamic Query

Question

I have scenario were I have a master table which stores db table name and column name, I need to build dynamic query based on that.

CREATE TABLE MasterTable
(
    Id int primary key,
    caption varchar(100),
    dbcolumnname varchar(100),
    dbtablename varchar(100)
);

CREATE TABLE Engineers
(
    Id int primary key,
    Name varchar(100),
    Salary BigInt
);

CREATE TABLE Executives
(
    Id int primary key,
    Name varchar(100),
    Salary BigInt
);

CREATE TABLE Manager
(
    Id int primary key,
    Name varchar(100),
    Salary BigInt
);

INSERT INTO Manager(Id, Name, Salary)
VALUES(1, 'Manager 1', 6000000);
INSERT INTO Executives(Id, Name, Salary)
VALUES(1, 'Executive 1', 6000000);
INSERT INTO Engineers(Id, Name, Salary)
VALUES(1, 'Engineer 1', 6000000);
INSERT INTO MasterTable(Id, caption, dbcolumnname, dbtablename)
VALUES (1, 'Name', 'name', 'Engineers');
INSERT INTO MasterTable(Id, caption, dbcolumnname, dbtablename)
VALUES (2, 'Name', 'name', 'Manager');
INSERT INTO MasterTable(Id, caption, dbcolumnname, dbtablename)
VALUES (3, 'Name', 'name', 'Executives');
INSERT INTO MasterTable(Id, caption, dbcolumnname, dbtablename)
VALUES (4, 'Salary', 'Salary', 'Engineers');
INSERT INTO MasterTable(Id, caption, dbcolumnname, dbtablename)
VALUES (5, 'Salary', 'Salary', 'Manager');
INSERT INTO MasterTable(Id, caption, dbcolumnname, dbtablename)
VALUES (6, 'Salary', 'Salary', 'Executives');

I want to build a stored procedure which accepts caption and Id and give result back based on dbcolumnname and dbtablename. For example if I pass Salary,Name as caption and Id as 1, stored procedure should be query of dbcolumn and dbtable, something like below.

Select Id as ID, name as Value from Engineers
UNION
Select Id as ID, name as Value from Manager
UNION
Select Id as ID, name as Value from Executives
UNION
Select Id as ID, Salary as Value from Executives
UNION
Select Id as ID, Salary as Value from Engineers
UNION
Select Id as ID, Salary as Value from Manager

I have heard of dynamic sql, can that be used here?

Fiddle

EDIT :: I got one dynamic query which builds union statement to get the output, however problem is i am not able to escape double quotes. Below is the query and Error

Query : 

DO
$BODY$
BEGIN

 EXECUTE string_agg(
    format('SELECT %I FROM %I', dbcolumnname, dbtablename),
    ' UNION ')
  FROM  MasterTable;

END;
$BODY$;

Error:
ERROR:  relation "Engineers" does not exist
LINE 1: SELECT name FROM "Engineers" UNION SELECT name FROM "Manager...

Dynamic query is very simple - just construct everything in string variable and use `execute yourvariable` in you function. But I think you should reconsider your data model. Looks to me a bit too complicated for task you want to achieve... — JosMac, Jan 11 '17 at 12:54
@JosMac Yes it is complicated, However this is not my data model. I have created a dummy model as i cannot post original data on forums. Can i use multiple table name in dynamic query or else i have to go for cursor which would be costly i believe. — Manthan Davda, Jan 11 '17 at 12:58
You can use any query you want in dynamic query. Complicated CTE event with inserts/ updates etc. There are no limitations. — JosMac, Jan 11 '17 at 13:00
`s/CREATE TABLE Executives/CREATE TABLE "Executives"/` And similar for the other tables. Or, even better: avoid MixedCase names for tables and columns. — wildplasser, Jan 11 '17 at 15:11

Fabian Pijcke · Answer 1 · 2017-01-11T15:55:32.260

I'd like to suggest an alternative way of achieving what you want. That is, using PostgreSQL inheritance mechanism.

For instance:

CREATE TABLE ParentTable (
    Id int,
    Name varchar(100),
    Salary BigInt
);

ALTER TABLE Engineers INHERIT ParentTable;
ALTER TABLE Executives INHERIT ParentTable;
ALTER TABLE Manager INHERIT ParentTable;

SELECT Id, Salary AS value FROM ParentTable
UNION
SELECT Id, Name AS value FROM ParentTable;

Now if you want to use MasterTable in order to restrict the set of tables used, you can do it as follows:

SELECT Id, Name AS value
FROM ParentTable
INNER JOIN pg_class ON parenttable.tableoid = pg_class.oid
INNER JOIN MasterTable ON LOWER(dbtablename) = LOWER(relname)
UNION
SELECT Id, Salary AS value
FROM ParentTable
INNER JOIN pg_class ON parenttable.tableoid = pg_class.oid
INNER JOIN MasterTable ON LOWER(dbtablename) = LOWER(relname)

However, you can not arbitrarily restrict the set of columns to retrieve from one table to another with this technique.

wildplasser · Answer 2 · 2017-11-25T14:40:05.493

Table names and column names are case insensitive in SQL, unless they are quoted in double quotes. Postgres does this by folding unquoted identifiers to lower case.

So, your DDL:

CREATE TABLE MasterTable
(
    Id int primary key,
    caption varchar(100),
    dbcolumnname varchar(100),
    dbtablename varchar(100)
);

Will be interpreted by Postgres as

CREATE TABLE mastertable
(
    id int primary key,
    caption varchar(100),
    dbcolumnname varchar(100),
    dbtablename varchar(100)
);

You can avoid case folding by quoting the names:

CREATE TABLE "MasterTable"
(
    "Id" int primary key,
    caption varchar(100),
    dbcolumnname varchar(100),
    dbtablename varchar(100)
);

The %I format-specifier (internally uses quote_ident()) adds quotes to its argument (when needed) , so the query asks for "MasterTable" when only mastertable is present in the schema.

But, it is easyer to just avoid MixedCase identifiers,

Postgres Dynamic Query

2 Answers2