Как автоматизировать подстановку FK в PostgreSQL?

Question

Делаю файл populate.sql, который будет использоваться для заполнения базы данных (PostgreSQL) тестовыми значениями. В базе очень много таблиц и различных связей, поэтому делать тестовые значение непросто. Вот для примера кусок populate.sql:

INSERT INTO themes(name)
VALUES ('Математика'),
       ('Русский язык');

INSERT INTO paragraphs(name, theme_id)
VALUES ('Операции с дробями', 100002);

Сначала я создаю запись в theme, потом создаю запись в paragraphs, которая ссылается на theme. Причём вот здесь: ('Операции с дробями', 100002) я вынужден вручную прописывать FK.

Теперь представим, что я позже захотел добавить в тестовые данные ещё одну тему:

INSERT INTO themes(name)
VALUES ('Математика'),
       ('Русский язык'),
       ('Обществознание');

INSERT INTO paragraphs(name, theme_id)
VALUES ('Операции с дробями', 100002);

И теперь мне надо вручную поменять FK с 100002 на 100003. Причём в базе данных всего 21 таблица, связей очень много, и на каждое небольшое изменение будет уходить не менее получаса.

Я хочу автоматизировать процесс подстановки FK, сделать что-то вроде такого:

INSERT INTO paragraphs(name, theme_id)
VALUES ('Операции с дробями', (SELECT themes.id FROM themes WHERE themes.name = 'Математика'));

но это не работает. Или мне надо каким-то образом сохранять нужный мне FK в переменную, чтобы потом использовать её несколько раз и менять только в одном месте.

Как я могу решить данную проблему?

Answer 1

Решил проблему, используя функции. Моя функция принимает имя theme и возвращает соответствующий id.

CREATE FUNCTION get_theme_id(VARCHAR) RETURNS INTEGER AS
'SELECT themes.id
 FROM themes
 WHERE themes.name = $1' LANGUAGE sql;

В скобках указывается тип принимаемого значения. После RETURNS указывается тип возвращаемого значения, а внутри апострофов размещено тело функции. Теперь я получаю FK таким образом:

INSERT INTO paragraphs(name, theme_id)
VALUES ('Операции с дробями', get_theme_id('Математика'));

Я решил не использовать PL/PgSQL потому что оператор RETURNING возвращает ошибку, если пытаться обработать более одной строки. Поэтому вот это работает:

INSERT INTO themes(name)
    VALUES ('Математика')
    RETURNING id INTO running_id;

но вот это уже не работает:

INSERT INTO themes(name)
        VALUES ('Математика'),
               ('Русский язык')
        RETURNING id, id INTO math_id, rus_lang_id;

На самом деле существует возможность заставлять RETURNING обрабатывать несколько значений, но моего уровня в SQL оказалось недостаточно для того, чтобы разобраться с этим. Решение заключается в использование массивов или что-то в этом роде - я не стал с этим разбираться, так как мне нерационально тратить время на поиски более правильного решение, тогда как функции вполне решают мою проблему. Просто предупреждаю, что RETURNING может обрабатывать несколько значений!

Answer 2

Лучше исползуйте returning clause. Пример на pl/pgsql:

do language plpgsql
$$
declare
  running_id integer;
begin
  INSERT INTO themes(name)
    VALUES ('Математика')
    RETURNING id INTO running_id ;

  INSERT INTO paragraphs(name, theme_id)
    VALUES ('Операции с дробями', running_id);
end;
$$;

БЛОГ НА HUSL

Как автоматизировать подстановку FK в PostgreSQL?

Ответы (2 шт):