sql - 构建动态查询 Postgres

Question

我有一张表，其中存储了每个实体(员工、部门)的详细信息。我想从该表构建动态查询。

CREATE TABLE MyTable
(
    Id int primary key,
    EntityId int,
    ColumnName varchar(100),
    tablename varchar(100)
);

INSERT INTO MyTable (Id, EntityId, ColumnName, tableName)
VALUES (1,1,'name','employee');

INSERT INTO MyTable (Id, EntityId, ColumnName, tableName)
VALUES (2,1,'id','employee');

INSERT INTO MyTable (Id, EntityId, ColumnName, tableName)
VALUES (3,1,'salary','employee');

INSERT INTO MyTable (Id, EntityId, ColumnName, tableName)
VALUES (4,2,'name','departement');

INSERT INTO MyTable (Id, EntityId, ColumnName, tableName)
VALUES (5,2,'location','departement');

INSERT INTO MyTable (Id, EntityId, ColumnName, tableName)
VALUES (6,2,'id','departement');

上面是我的表和插入脚本，我怎样才能编写一个查询，使我的输出如下所示。

SELECT id,name,salary from employee

SELECT id,location,name from departement

如果我有多个实体，我应该使用多个选择语句。

Answer 1

如果尽管评论令人沮丧，您仍然想考虑这种方法，这里是为每个实体构造一个查询的查询:

SELECT entityid,
  'SELECT ' ||
  string_agg(columnname, ', ' ORDER BY id) ||
  ' FROM ' ||
  tablename ||
  ';' AS query
FROM mytable
GROUP BY entityid, tablename;

你的例子的结果:

 entityid |                   query                   
----------+---------------------------------------------
        1 | SELECT name, id, salary FROM employee;
        2 | SELECT name, location, id FROM departement;
(2 rows)