Динамический запрос Postgres

У меня есть сценарий, в котором у меня есть главная таблица, в которой хранятся имя таблицы БД и имя столбца, мне нужно построить динамический запрос на основе этого.

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

Я хочу создать хранимую процедуру, которая принимает заголовок и идентификатор и возвращает результат на основе имени столбца базы данных и имени таблицы базы данных. Например, если я передам Salary, Name как заголовок и 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...

postgresql dynamicquery
person Manthan Davda    schedule 11.01.2017    source источник
comment
Динамический запрос очень прост - просто создайте все в строковой переменной и используйте execute yourvariable в своей функции. Но я думаю, вам следует пересмотреть свою модель данных. Мне кажется, это слишком сложно для задачи, которую вы хотите выполнить...   -  person JosMac    schedule 11.01.2017
comment
@JosMac Да, это сложно, однако это не моя модель данных. Я создал фиктивную модель, так как не могу размещать исходные данные на форумах. Могу ли я использовать несколько имен таблиц в динамическом запросе, иначе мне придется использовать курсор, который, как мне кажется, будет дорогостоящим.   -  person Manthan Davda    schedule 11.01.2017
comment
Вы можете использовать любой запрос в динамическом запросе. Сложное событие CTE со вставками/обновлениями и т.д. Ограничений нет.   -  person JosMac    schedule 11.01.2017
comment
s/CREATE TABLE Executives/CREATE TABLE "Executives"/ И аналогично для других таблиц. Или, что еще лучше: избегайте имен MixedCase для таблиц и столбцов.   -  person wildplasser    schedule 11.01.2017

Ответы (2)


arrow_upward
0
arrow_downward

Я хотел бы предложить альтернативный способ достижения того, что вы хотите. То есть с использованием механизма наследования 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)

Однако с помощью этого метода вы не можете произвольно ограничить набор столбцов для извлечения из одной таблицы в другую.

person Fabian Pijcke    schedule 11.01.2017

arrow_upward
0
arrow_downward

Имена таблиц и столбцов нечувствительны к регистру в 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,

person wildplasser    schedule 11.01.2017