Функция для просмотра и выбора данных из нескольких таблиц

Question

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

Я мог бы сделать это с кучей UNION запросов, но количество таблиц, которые мне нужно искать, может со временем меняться, поэтому я не хочу жестко кодировать это так. Я пытался разработать функцию, которая будет проходить через определенные таблицы (у них общее соглашение об именах) и возвращать таблицу записей, но я не получаю никаких результатов, когда запрашиваю функцию. Код функции ниже:

CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
  RETURNS TABLE(natural_id text, name text, natural_id_numeric text) AS
$BODY$
DECLARE
    formal_table text;
begin
  FOR formal_table IN
    select table_name from information_schema.tables
    where table_schema = 'public' and table_name like 'formaltable%'
  LOOP
    EXECUTE 'SELECT natural_id, name, natural_id_numeric
             FROM ' || formal_table || 
           ' WHERE natural_id_numeric IN (
                select natural_id_numeric from internal_idlookup
                where internal_id = ''7166571'')';
    RETURN NEXT;
 END LOOP;
 Return;
END;
$BODY$
  LANGUAGE plpgsql;

Я не получаю никаких ошибок, когда пытаюсь использовать функцию, но она не возвращает никаких строк:

SELECT * From internalid_formaltable_name_lookup();

Есть идеи, где я ошибся?

Answer 1

CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
  RETURNS TABLE(natural_id text, name text, natural_id_numeric text) AS
$func$
DECLARE
   formal_table text;
BEGIN
   FOR formal_table IN
      SELECT quote_ident(table_name)
      FROM   information_schema.tables
      WHERE  table_schema = 'public'
      AND    table_name LIKE 'formaltable%'
   LOOP
      RETURN QUERY EXECUTE
      'SELECT t.natural_id, t.name, t.natural_id_numeric
       FROM   internal_idlookup i 
       JOIN   public.' || formal_table || ' t USING (natural_id_numeric)
       WHERE  i.internal_id = 7166571';   -- assuming internal_id is numeric
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Основные моменты:

• Вы должны использовать RETURN QUERY EXECUTE вернуть каждый набор строк.
  EXECUTE, с последующим RETURN NEXT, не делает то, что вы, кажется, ожидаете вообще.

• Вам нужно продезинфицировать идентификаторы. я использую quote_ident() Вот. Или ваш запрос будет разорван с нестандартными идентификаторами и позволит SQL-инъекцию!

• Преобразовал ваш col IN (sub-select) к более эффективному JOIN,

• Это немного отличается от использования a bunch of UNION queries, Он не удаляет повторяющиеся строки и фактически работает как UNION ALL,

Лично я предпочел бы построить это на системном каталоге pg_class, Подробности:

• Как проверить, существует ли таблица в данной схеме

Тогда вы можете работать с pg_class.oid::regclass для экранирования и уточнения имен таблиц автоматически. Подробности:

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

Но это зависит от деталей ваших требований и... вкуса.