Поиск по нескольким таблицам, а также отображение имени таблицы в результирующих строках

Question

Как структурировать оператор SQL для работы с несколькими плоскими несвязанными таблицами и отображения результата с результатом выбора и именем таблицы, из которой получен результат.

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

Те же таблицы выглядят так:

Table 1: pid, parent_name, student_name, student_number, class_name, columnN
Table 2: pid, previous_school, previous_school, student_number, columnN
Table 3: pid, student_name, student_number, parent_name, column4, columnN
Table 14: pid, student_number, parent_name, column4, columnN
Table N: pid, previous_school, parent_name, column4, columnN

Мне нужен оператор SQL, который ищет student_name по всем таблицам в псевдокоде: for each table, find a student named john doe and return to me the row where you got the result and the table where you found the result

Дайте результат в следующей презентации:

john doe, Table 1, pid
john doe, Table 9, pid

Чтобы сделать это немного сложнее, столбец student_name может быть не во всех таблицах, поэтому запрос должен выполняться любезно, если столбец там не найден.

Answer 1

Вы ищете динамический SQL. Соберите ваш запрос из системного каталога автоматически:

SELECT string_agg('SELECT student_name, '''
                   || c.oid::regclass || ''' AS tbl, pid FROM '
                   || c.oid::regclass
                   || $$ WHERE student_name = 'John Doe'$$
                 , E'\nUNION ALL\n')
FROM   pg_namespace n
JOIN   pg_class     c ON c.relnamespace = n.oid
WHERE  n.nspname = 'public'         -- schema name where your tables lie
AND    c.relname LIKE 't%'          -- and / or filter table names
AND    EXISTS (
   SELECT 1 FROM pg_attribute 
   WHERE  attrelid = c.oid
   AND    attname = 'student_name'  -- make sure column exists
   AND    NOT attisdropped          -- and is alive
   );

Создает строку запроса:

SELECT student_name, 'tbl1' AS tbl, pid FROM tbl1 WHERE student_name = 'John Doe'
UNION ALL
SELECT student_name, 'tbl2' AS tbl, pid FROM tbl2 WHERE student_name = 'John Doe'
UNION ALL
SELECT student_name, 'tbl3' AS tbl, pid FROM tbl3 WHERE student_name = 'John Doe'
...

Затем запустите его во втором вызове или полностью автоматизируйте с помощью функции PL/pgSQL, используя EXECUTE, Пример:
Выберите динамический набор столбцов из таблицы и получите сумму для каждого

Этот запрос создает безопасный код с очищенными идентификаторами, предотвращающими внедрение SQL. (Объяснение для oid::regclass Вот.)

Есть больше связанных ответов. Используйте поиск.

КСТАТИ, LIKE в student_name LIKE 'John Doe' бессмысленно. Без подстановочных знаков, просто используйте =,

Answer 2

Просто добавьте буквальное значение для каждого выбора, который описывает исходную таблицу:

select student_name, 'Table 1', pid
from table1
where ...
union
select some_name, 'Table 2', pid
from table2
where ...
union
...

Answer 3

Вы можете получить имена таблиц из представления all_tables, динамически создать запрос на объединение, а затем выполнить его с помощью команды немедленного выполнения. Примерно так: будьте осторожны, код может содержать ошибки:

DECLARE
  v_query VARCHAR2(4000) := '';
  v_student_name VARCHAR2(50) := 'John Doe';

  type r_results is record (
    student_name VARCHAR2(500),
    table_name VARCHAR2(100),
    pid NUMBER
  );

  v_results r_results;

  CURSOR c_tables IS
    SELECT table_name 
    FROM all_tables 
    WHERE upper(table_name) LIKE '%_STUDENT_RECORDS';
BEGIN      
  FOR client_table IN c_tables LOOP
    IF v_query IS NOT NULL THEN
      v_query := v_query || ' UNION ';
    END IF;

    v_query := v_query || 'SELECT student_name, ' || client_table.table_name || ', ' || pid FROM ' || client_table.table_name || ' WHERE student_name = ''' || v_student_name || '''';
  END LOOP;


  EXECUTE IMMEDIATE v_query INTO v_results;
END;