Postgres Dynamic Query

Question

我有一个存储数据库表名称和列名的主表，我需要基于此构建动态查询。

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');

我想构建一个存储过程，它接受标题和 Id，并根据 dbcolumnname 和 dbtablename 返回结果。例如，如果我传递薪水，名称作为标题，Id 作为 1，存储过程应该是 dbcolumn 和 dbtable 的查询，如下所示。

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

我听说过动态sql，可以在这里使用吗？

小提琴

编辑 ：： 我有一个动态查询，它构建联合语句以获取输出，但是问题是我无法转义双引号。下面是查询和错误

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...

Answer 1

我想提出一种实现您想要的替代方法。也就是说，使用PostgreSQL继承机制。

例如：

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;

现在，如果要使用主表来限制使用的表集，可以执行以下操作：

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)

但是，不能使用此技术任意限制要从一个表检索到另一个表的列集。

Answer 2

表名和列名在 SQL 中不区分大小写，除非它们用双引号引起来。Postgres 通过将不带引号的标识符折叠为小写来做到这一点。

因此，您的 DDL：

---

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

---

将被Postgres解释为：

---

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

---

您可以通过引用名称来避免大小写折叠：

---

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

---

%I格式说明符（内部使用 quote_ident（））在其参数中添加引号（需要时），因此当架构中仅存在mastertable时，查询会要求"MasterTable"。

但是，避免混合大小写标识符更容易，