PostgreSQL, почему не подставляется переменная «TG_TABLE_NAME»?

Question

[P747]

Добрый день!

Почему возникает ошибка и не подставляется данные из переменной?



Код триггера и функции:

CREATE OR REPLACE FUNCTION update_dt_column() RETURNS TRIGGER AS $$
BEGIN
    UPDATE TG_TABLE_NAME SET dt_update = NOW() WHERE id = NEW.id;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_phones_dt_update
AFTER UPDATE ON phones FOR EACH ROW EXECUTE PROCEDURE update_dt_column();

Answer 1

[Melkij]

Потому что с чего вы решили, что в pl/pgsql есть синтаксис update переменная set? Нет его. На этом месте всегда имя таблицы.

Впрочем, триггер вовсе абсурден и ничего кроме бесконечной рекурсии не породит.
Вам нужен before insert триггер с

NEW.dt_update = NOW();
return NEW;

Comments

[P747]

Потому что с чего вы решили, что в pl/pgsql есть синтаксис update переменная set? Нет его. На этом месте всегда имя таблицы.

Не совсем пойму о чем вы, в теле функции же обычный sql. Или как правильно?

Вам нужен before insert триггер с

Почему before insert? Мне по сути нужно после обновления записи

[Melkij]

Не совсем пойму о чем вы, в теле функции же обычный sql. Или как правильно?

Ну, и синтаксиса "update переменная set ..." - нет. Там всегда имя таблицы, никто и не собирается проверять, а не переменную ли вы туда хотите сделать. Сказали UPDATE TG_TABLE_NAME - значит вы хотите выполнить операцию над таблицей с таким названием tg_table_name.

Почему before insert? Мне по сути нужно после обновления записи

Потому что наверняка id у вас первичный ключ, следовательно вы пытаетесь обновить ещё раз ту самую одну строку, обновление которой вызвало этот триггер. Чем породите бесконечную рекурсию до исчерпания стека.
А на самом деле вы хотите изменить ту строку, которую записываете, обновив в ней dt_update на время начала этой транзакции. Это делается перед записью модификацией самого NEW.

[P747]

Melkij, А способ универсально подставлять значения для разных таблиц есть? Мне просто нужно для разных таблиц эту функцию вызывать

[Melkij]

Вам третий раз писать, что для вашей задачи TG_TABLE_NAME не нужен совсем?

[P747]

Melkij, Спасибо, понял вас. Сейчас попробовал не срабатывает, ошибок нет, посмотрите пожалуйста в чем проблема, может что-то не то написал:

CREATE OR REPLACE FUNCTION update_dt_column() RETURNS TRIGGER AS $$
BEGIN
    NEW.dt_update = NOW();
    return NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_phones_dt_update
BEFORE INSERT ON phones FOR EACH ROW EXECUTE PROCEDURE update_dt_column();

[Melkij]

melkij=> begin;
BEGIN
melkij=*> create table phones ( id serial, dt_update timestamptz);
CREATE TABLE
melkij=*> CREATE OR REPLACE FUNCTION update_dt_column() RETURNS TRIGGER AS $$
melkij$*> BEGIN
melkij$*>     NEW.dt_update = NOW();
melkij$*>     return NEW;
melkij$*> END;
melkij$*> $$ LANGUAGE plpgsql;
CREATE FUNCTION
melkij=*> CREATE TRIGGER t_phones_dt_update
melkij-*> BEFORE INSERT ON phones FOR EACH ROW EXECUTE PROCEDURE update_dt_column();
CREATE TRIGGER
melkij=*> insert into phones (id) values (default);
INSERT 0 1
melkij=*> table phones;
 id |           dt_update           
----+-------------------------------
  1 | 2020-10-27 13:16:07.956215+03

works

[Melkij]

ааа, вот откуда вы before insert взяли, это я не то пишу в ответе. before update, конечно, вместо after update бывшего в вопросе.

[P747]

Melkij, У меня на UPDATE записи не обновляет. Ни с треггером BEFORE INSERT ни AFTER UPDATE

[P747]

Melkij, BEFORE UPDATE сработало. Спасибо)