Postgres动态查询

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

我想构建一个接受caption和Id的存储过程，并根据dbcolumnname和dbtablename返回结果。例如，如果我传递Salary，Name作为标题，Id作为1，那么存储过程应该是query of 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，可以在这里使用吗？

Fiddle

我得到了一个动态查询，它构建联合语句来获得输出，但是问题是我不能转义双引号。下面是查询和错误

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;

现在，如果您想要使用MasterTable来限制所使用的表格集合，您可以执行以下操作：

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。

但是，更容易避免使用MixedCase标识符，